currently banging head against wall.......

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

currently banging head against wall.......

Author

Topic

kevla22
Starting Member

7 Posts

Posted - 2006-04-04 : 11:29:04

Hey guru's,

using the following script to rename a text file following a DTS export with a datetime stamp which works ok, i am trying to make the file path dynamic based on a string value in a table in my database:

'**********************************************************************
' Visual Basic ActiveX Script to rename filename with datetime stamp
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success
End Function

set myconn = CreateObject("adodb.connection")
connection = "Provider=SQLOLEDB;" & "Data Source=(LOCAL);" & "Initial Catalog=Questar;" & "Integrated Security=SSPI"

myconn.open (connection)
set result = CreateObject("adodb.recordset")

SQL = "SELECT Outbound FROM dbo.taglist WHERE outbound <> 'NULL'"
set result = myconn.execute(SQL)

Dim StrAccessSrc, StrNew, fso, sf, systime
StrAccessSrc = result
Set fso = CreateObject("Scripting.FileSystemObject")
Set saf = fso.GetFile(StrAccessSrc)
systime = Now()

Call RenameSFile(saf)

Sub RenameSFile(sf)
StrNew = sf.ParentFolder & "\SD_" & CStr(Day(systime)) & CStr(Month(systime)) & CStr(Year(systime)) & CStr(Hour(systime)) & CStr(Minute(systime)) & CStr(Second(systime)) & ".ds5"
sf.Move StrNew
End Sub

When executing the task i get the error:

CANNOT OPEN DATABASE REQUESTED IN LOGIN 'QUESTAR'. LOGIN FAILS

I have also tried switching mixed mode on and specifying the sa username and password, no matter which way i try i get a security error message.

Anyone got any ideas?

cheers guys

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 11:35:44

Just an idea

Does ur user (should be a windows user as well I hope) has rights to create folders on the location u specified ?

Srinika

kevla22
Starting Member

7 Posts

Posted - 2006-04-04 : 12:04:38

Hi Srinika,

Sorry i should have been more specific, the part creating the problem is this bit:

set myconn = CreateObject("adodb.connection")
connection = "Provider=SQLOLEDB;" & "Data Source=(LOCAL);" & "Initial Catalog=Questar;" & "Integrated Security=SSPI"

myconn.open (connection)
set result = CreateObject("adodb.recordset")

SQL = "SELECT Outbound FROM dbo.taglist WHERE outbound <> 'NULL'"
set result = myconn.execute(SQL)

The error occurs when the script tries to open the connection to the database, there is something not right with the security, i have patched and installed sp4, spent all day fiddling with users and roles and it still won't work, i am starting to think SQL needs a re-install.

Cheers

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 12:16:47

In ur Connection don't u need to give UserName & Pswd ?

Srinika

kevla22
Starting Member

7 Posts

Posted - 2006-04-05 : 04:18:49

Hi Srinika,

Using "Integrated Security=SSPI" means that no username and password is needed, it uses the current logon with windows authentication.
To specify a logon means switching to mixed mode, which i have tried with the sa username and password, but i get a similar message!

its a little frustrating to say the least.

cheers

kevla22
Starting Member

7 Posts

Posted - 2006-04-05 : 05:05:19

Hi,

found an answer!

Set myconn = CreateObject("adodb.connection")
Connection = "Provider=SQLOLEDB;" & "Data Source=server_name\LOCAL;" & "Initial Catalog=Questar;" & "Trusted_Connection=Yes"

This works fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources