DTS Question 1

[Bell1991]

Is it possible to call a DTS from a stored procedure?

What i need to do - is call a stored procedure that calls a DTS and retuns a database. Is this possible? I have read that it is possible to call a DTS from a stored procedure - but nothing says about getting the database back to the application that has called it.

Thanks in advance.

 

[JayKusch]

You could do one of two things. You could use DTSRun command to start the package off from within an SP or you could create a job from the DTS package and the execute an sp_start_job to fire off the job which in turn fires off the DTS. You can schedule a job by right-mouse clicking the DTS package and choosing to schedule it. It will the create a job in the Jobs folder in Enterprise Manager under the SQL Server Agent folder. You can then open this job and DISABLE it to run at a predefined schedule. Then you can call the job from w/in the SP whenever needed.

Thanks

J. Kusch

 

[JayKusch]

Here is a straight pull from BOL (BooksOnLine) on the syntax for DTSRun, which seems to be the route you need to take ...

To execute a DTS package saved in the SQL Server msdb database, use:

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password

Thanks

J. Kusch

 

[Jimmy211]

JayKusch,

I just tried the suggestion of starting a job from a stored procedure in my database. The sql statement looks like this:

EXEC sp_start_job @job_name = 'AllocDataUpdateTask2'

where 'AllocDataUpdateTask2' is the job. Unfortunately it didn't work. The error message was "cannot find stored procedure 'sp_start_job'. Apparently this command is stored in the msdb system database. I can use the query analyzer to successfuly use this command with the following:

USE msdb
EXEC sp_start_job @job_name = 'AllocDataUpdateTask2'

So I know that this works, but you have to reference the msdb database as part of it. If I use this code in my stored procedure it tells me I can't save it because the USE statement isn't allowed in a procedure.

Any way around this problem?

Thanks,
Jimmy211

 

[JayKusch]

EXEC msdb..sp_start_job @job_name = 'AllocDataUpdateTask2'


Thanks

J. Kusch