run DTS from SP

[spiff2002]

Is there a way to run a DTS package from a stored procedure?
The idea is to trigger a package from an ASP webpage. The Package cannot be shcedule because it has to be run by a user that needs to finish some tasks befero he can run the package.
If there is a way can you post the code to create the package since I'm not too familiar with stored procedures

Thanks a million

S.

 

[philhege]

Gotta have msdb rights, but check into using something like

exec msdb.dbo.sp_add_jobserver @job_name = 'job_#ucase(l_user)#'
exec msdb.dbo.sp_start_job @job_name = 'job_#ucase(l_user)#'

This is a snippet of some statements I use to make a self-destructing job that calls an SP.

Create the job for the DTS package from within the EM interface (right-click on the package and choose "Schedule Package..."). Use the new job's name in the statements above. You may have to issue other sp_job-type statements for permissions, etc.

Read up about sp_...jobs in BOL for more info.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
In one hundred years, none of this will matter. Keep a healthy perspective!