SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Starting a Job


Starting a Job

Author
Message
rightfield
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39271 Visits: 38529
Check BOL (Books Online). The procedure you are looking for is msdb.dbo.sp_start_job (or in BOL, just sp_start_job).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Michael Earl-395764
Michael Earl-395764
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4895 Visits: 23078
msdb..sp_help_job will return the status of job(s) to determine if they are currently running.
rightfield
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
Thank you both. Warm regards, Hope
rightfield
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
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


Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39271 Visits: 38529
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



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
rightfield
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
Yes, thank you.
rightfield
rightfield
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 1070
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39271 Visits: 38529
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
KumarVelayutham
KumarVelayutham
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 1338
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search