Starting a Job

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Starting a Job

11 posts, Page 1 of 2

1 2 »»

Starting a Job

Rate Topic

Display Mode

Topic Options

Author

Message

rightfield

Posted Monday, December 15, 2008 11:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

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

Post #619831

Lynn Pettis

Posted Monday, December 15, 2008 11:38 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 21,623, Visits: 27,456

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)

Post #619850

Michael Earl-395764

Posted Monday, December 15, 2008 12:04 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

msdb..sp_help_job will return the status of job(s) to determine if they are currently running.

Post #619878

rightfield

Posted Monday, December 15, 2008 12:07 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

Thank you both. Warm regards, Hope

Post #619880

rightfield

Posted Monday, December 15, 2008 12:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

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

Post #619892

Lynn Pettis

Posted Monday, December 15, 2008 1:14 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 21,623, Visits: 27,456

h.schlais (12/15/2008)

Hi Lynn, I get the following error:

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

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #619933

rightfield

Posted Monday, December 15, 2008 1:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

Yes, thank you.

Post #619942

rightfield

Posted Monday, December 15, 2008 2:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 07, 2013 4:28 PM
Points: 114, Visits: 1,062

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

Post #619994

Lynn Pettis

Posted Monday, December 15, 2008 2:41 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 21,623, Visits: 27,456

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

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #620017

KumarSQLDBA

Posted Thursday, December 27, 2012 5:12 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 29, 2013 4:03 AM
Points: 81, Visits: 824

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
http://www.sqldbatask.blogspot.com/

Post #1400568

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions