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

[meyvn77]

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

[nicolec]

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

[Erland Sommarskog]

(me*****@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, es****@sommarskog.se

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