Help with calling a job from Stored P and VBA......HELP

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,
ChuckHere's a sample that pulls the variable from a form and passes it to a
stored procedure:

'Declare variables
Dim cmd As ADODB.Command
Dim prm As Parameter

'Set connection and command properties
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spMyProcedure"

'Set parameters
Set prm = cmd.CreateParameter("@.myVariable", adInteger, adParamInput)
cmd.Parameters.Append prm
prm.Value = Forms!myForm.myVariable.Value

'Run Command
cmd.Execute

'Cleanup resources
Set cmd = Nothing
Set prm = Nothing|||(meyvn77@.yahoo.com) writes:
> I would like to know if it is possible to start a job from a stored
> procedure?

Yes. As long as you have the privileges.

> 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'"

Maybe better to add an adExecuteNoRecords? This could be the reason
you don't see any error message.

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

And SQL Server is running?

Have you checked in job history that the job is not terminating directly?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp