Hi,

We have a DTS job that is being run from SQL Server Agent on a SQL
Server 2000 box. When the job runs it runs the job twice for some
reason. The DTS job is composed of an Active X script that runs a
stored procedure and uses COM components.

Now when I run the DTS job by hand it only executes once, but when run
from SQL Server Agent it runs twice only milliseconds apart.

The call to "proc_list_scheduled_system_tasks_due" always will return
one record for the test case I am trying to work on so there is no
cause for alarm there.

Could it have something to do with executing the command asyncronously?

oCmd.Execute ,,adAsyncExecute

Any help is appreciated!

Here is the code:

const adCmdStoredProc = 4
const adInteger = 3
const adParamInput = 1
const adVarchar = 200
const adAsyncExecute = 16

Function Main()

dim oCmd
dim oRs
dim oVariablesRs
dim sUser
dim sDatabase
dim sPassword
dim lScheduledActivityId
dim oConn
dim lScheduledTaskId
dim sConnectString

sConnectString = DTSGlobalVariables("TASK_MANAGER_CONNECT").Value

set oConn = CreateObject("ADODB.Connection")
set oCmd = CreateObject("ADODB.Command")

oConn.Open = sConnectString

Set oCmd.ActiveConnection = oConn

oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "proc_list_scheduled_system_tasks_due"
set oRs = oCmd.Execute()

while not oRs.EOF

lScheduledTaskId = oRs("t007_id")
asdt_RunJob oConn, lScheduledTaskId

oRs.MoveNext

Wend

Main = DTSTaskExecResult_Success

set oCmd = nothing

end function

function asdt_RunJob(oConn, lScheduledTaskId)

dim oCmd
dim oRs
dim sLog

set oCmd = CreateObject("ADODB.Command")

Set oCmd.ActiveConnection = oConn

oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "proc_run_dts_job"
oCmd.Parameters.Append oCmd.CreateParameter("@job_name", adVarchar,
adParamInput, 50, "TASK_MANAGER_RUN_TASK")
oCmd.Parameters.Append oCmd.CreateParameter("@t007_id", adInteger,
adParamInput, 0, lScheduledTaskId)
oCmd.Parameters.Append oCmd.CreateParameter("@t004_id", adInteger,
adParamInput, 0, 0)

oCmd.Execute ,,adAsyncExecute

asdt_RunJob = True

end function