December 15, 2008 at 11:16 am
Hi, I would like to have a procedure start a job, is that possible to do and to do it without waiting for it to finish. Also I tried to find in various msdb..sysjob.... how to check if the job is executing. Is this possible?
Warm regards, Hope
December 15, 2008 at 11:38 am
Check BOL (Books Online). The procedure you are looking for is msdb.dbo.sp_start_job (or in BOL, just sp_start_job).
December 15, 2008 at 12:04 pm
msdb..sp_help_job will return the status of job(s) to determine if they are currently running.
December 15, 2008 at 12:07 pm
Thank you both. Warm regards, Hope
December 15, 2008 at 12:32 pm
Hi Lynn, I get the following error:
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.sp_start_job'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
my call is:
IF (DATEPART(WEEKDAY,GETDATE())=1 OR DATEPART(WEEKDAY,GETDATE())=7)
BEGIN
EXEC dbo.sp_start_job @job_name='ManualSync',@server_name='SQL_01, @step_name='Update_1'
END
December 15, 2008 at 1:14 pm
h.schlais (12/15/2008)
Hi Lynn, I get the following error:Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.sp_start_job'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
my call is:
IF (DATEPART(WEEKDAY,GETDATE())=1 OR DATEPART(WEEKDAY,GETDATE())=7)
BEGIN
EXEC dbo.sp_start_job @job_name='ManualSync',@server_name='SQL_01, @step_name='Update_1'
END
You're missing something very important, see below:
IF (DATEPART(WEEKDAY,GETDATE())=1 OR DATEPART(WEEKDAY,GETDATE())=7)
BEGIN
EXEC msdb.dbo.sp_start_job @job_name='ManualSync',@server_name='SQL_01, @step_name='Update_1'
END
December 15, 2008 at 1:23 pm
Yes, thank you.
December 15, 2008 at 2:20 pm
Hi Lynn, if someone someday changes the name of the job my procedure would break. Where do I find the job_id? It is less likely that someone will delete the job. Warm regards, Hope
December 15, 2008 at 2:41 pm
You will find it here: msdb.dbo.sysjobs
If you use the job_id, if they drop and recreate your job, the procedure will also break.
Best thing to do is document your job and that it is executed from your specified stored procedure.
December 27, 2012 at 5:12 am
How can i start the next job step of job in the same job?
I have tried like '--EXEC msdb.DBO.sp_start_job @job_name ='dhhjdhjj', @server_name = N'Sdhjhjfhjfhj',@step_name ='step1'
But getting the below error
Message
Executed as user: ssss-agent. SQLServerAgent Error: Request to run job 'dhhjdhjj' (from User ssss-agent) refused because the job is already running from a request by User PROD\5555ff. [SQLSTATE 42000] (Error 22022). The step failed.
Need:If records exists in the first step of job output,,next step of the same job will execute ,otherwise quit.
Try to resolve this error?
Regards,
Kumar
December 27, 2012 at 6:53 am
KumarSQLDBA (12/27/2012)
How can i start the next job step of job in the same job?I have tried like '--EXEC msdb.DBO.sp_start_job @job_name ='dhhjdhjj', @server_name = N'Sdhjhjfhjfhj',@step_name ='step1'
But getting the below error
Message
Executed as user: ssss-agent. SQLServerAgent Error: Request to run job 'dhhjdhjj' (from User ssss-agent) refused because the job is already running from a request by User PROD\5555ff. [SQLSTATE 42000] (Error 22022). The step failed.
Need:If records exists in the first step of job output,,next step of the same job will execute ,otherwise quit.
Try to resolve this error?
First, it is obvious from the error that the job is already running. Second, if you started a multistep job at step 1 it will run all the steps in the job not just the first step. Same goes if you started it at step 2, it would run all steps starting at step 2.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply