sql server dts: I would like to know if it is possible to start a job from a stored
procedure?

I have a DTS that I set as a job and would like to either call it from
an ADP with

Conn.Execute "EXEC msdb..sp_start_job @job_name = 'Volusia'"

OR just strat it with a stored procedure and call the stored procedure
from the adp


CREATE PROCEDURE sde.Volusia_Import AS
EXEC msdb..sp_start_job @job_name = 'Volusia_Import'
GO


I tried both of these and it does not give me an error but it does not
run the job... what am I missing?

Thanks,
Chuck

You can run DTS packages with the OLE Automation objects or with
xp_cmdshell to run dtsrun.exe. You can find out about dtsrun in Books
Online, and an example using the OLE Automation objects appears below.

I'm copying this example from
http://www.mssqlcity.com/faq/devel/dtsviaqa.htm :

DECLARE @object int
DECLARE @hr int

--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN
END

EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile',
NULL, 'C:\NewPack.dts', ''
IF @hr <> 0
BEGIN
print 'error LoadFromStorageFile'
RETURN
END

EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
RETURN
END

Good luck,
Tony Sebion

[quoted text, click to view]