DTS and sp_start_job question

[tman135]

Getting into DTS pretty heavy now and I need the flexiblility to execute a DTS package from my stored procedures.
Ultimately I will run the DTS by running a stored proc from ASP.

I've reviewed all of the DTS and stored proc related posts, but am a little confused.....
xp_cmdshell...dtsrun is command line and isn't available inside a stored proc...right?

So I figure I should use sp_start_job to run the DTS. The package is title 'Export_Data' and it is a local package on my SQL Server under Data Transformation Services.

My stored procedure inside my production database reads like this.

CREATE PROCEDURE DTS_SP_TEST AS

exec sp_start_job @job_name = 'Export_Data', @job_id = '{EXXXX-CC3A-4XXX-B9F1-08A8C3E4DDE1}'

Running the stored proc illicits this error message
Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'sp_start_job'.

but if I try to execute the sp_start_job directly, Iget this error
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('Export_Data') does not exist.

Am I missing something as to where my package is saved or something in the syntax of the identifiying the job?

Any help would be appreciated.

 

[tlbroadbent]

sp_start_job will start a JOB not a DTS package. If you have setup a Job to run the DTS package then the technique you describe will work. You can use xp_cmdshell to execute a DTS package from a stored procedure. Check the article at SQLDTS.com about executing a package from a stored procedure.

http://www.sqldts.com/main.asp?nav=1,6,210,0

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums. NOTE: Reference to the FAQ is not directed at any individual.