VB6 ( SP6 ) & SQL 2000

In my vb code i have a public connection to to the SQLServer by using the
following code

Set UserDbconnect = New ADODB.Connection
UserDbconnect.ConnectionTimeout = 60
UserDbconnect.ConnectionString = "DSN=MyDSN;UID=MYUID;PWD=MYPWD;"
UserDbconnect.Open

This works fine for executing Stored procedures and Select queries, however
when firing off a DTS, the only way i know how is using the code below


sServer = ServerName
sUsername = UserName
sPassword = UserPassword
sPackageName = DTSName

' Load Package
oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
DTSSQLStgFlag_Default, , , , sPackageName

Does anyone know of a way to use the curent database connection rather than
having to hard code in the server name etc ?

A few options using t-sql statements would be-
1. Use xp_cmdshell and execute dtsrun.
2. Use the automation stored procedures (sp_OAxxxx stored
procedures) to load and execute the package
You can find sample code in this article:
http://www.databasejournal.com/featu...le.php/1459181
3. You could put your dtsrun to execute the package in a job
and just call sp_start_job.

-Sue

On Tue, 22 Mar 2005 02:37:04 -0800, Peter Newman
<PeterNewman (AT) discussions (DOT) microsoft.com> wrote: