In AxctiveX Script I was dynamically changing the server and database of an
existing connection as I was looping through a recordset and then executing a
SQL Task that was set to that exisitng Connection (see code example below).

Everthing worked beautifully as long as I was switching databases
(oCnn.Catalog) within the same Server (oCnn.DataSource). I could change to 3
different databases on the same server and everything worked, but as soon as
I tried to change the server and the database it would fail. Is there any
way in code to reset the server (oCnn.DataSource) of an exisitng connection
so that I can change servers?

Code Example:

Set rs=DTSGlobalVariables("gvRS").Value
If rs.RecordCount>0 then
Do Until rs.EOF=True
'Set up Destination Output Server & Database Connection
set oCnn = DTSGlobalVariables.Parent.Connections("OutputServe r")
oCnn.DataSource =rs("SERVER_NAME")
oCnn.Catalog =rs("DATABASE_NAME")

'Enable the existing step (Execute SQL Task) and run the SQL
set oStep = DTSGlobalVariables.Parent.Steps("TruncateOutputAID ata")
oStep.DisableStep=False
oStep.Execute
oStep.DisableStep=True

rs.MoveNext
Loop

Every idea I have come up with to work around this problem has failed. I
appreciate any help I can get!

What was the error message?



"JSP" <JSP (AT) discussions (DOT) microsoft.com> wrote

Hi,

The message was:

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Could not locate entry in sysdatabases for database
'AgencyDataRepositoryJulie'. No entry found with that name. Make sure that
the name is entered correctly.
Step Error code: 80004005

The step is pointing to the existing connection that I changed the server
(oCnn.DataSource) in, in the code as shown in my example. The interesting
thing is if you run the job interactively and stop and look at the exisitng
connection the new entries that I made in code are there (and the server and
database exist). But what it appears to me is that even though the server
connection changed when I display the vaues in the disconnected edit of the
connection, the actual system performing the code does not seem to release
the connection and is still looking at the first server that was there not
the new one that I changed to. The Database "'AgencyDataRepositoryJulie"
that the error was referring to does exist on the server I was changing to
but does not exist on the previous server connection.

Any ideas on how to to force the system to update the existing connection?

JSP

"Allan Mitchell" wrote:

Have you looked at the "Close Connection On Completion" workflow
property?

"JSP" <JSP (AT) discussions (DOT) microsoft.com> wrote

Thanks it worked! I set the last SQL Task in the loop to "Close Connection
On Completion" and then in the next loop checked the connection:

If oCnn.Connected=False then oCnn.ConnectImmediate=True
End If

This worked perfectly and swithed servers as needed. Thanks again!

JSP
"Allan Mitchell" wrote: