Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Starting a Job Expand / Collapse
Author
Message
Posted Monday, December 15, 2008 11:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:16 AM
Points: 114, Visits: 1,065
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
Posted Monday, December 15, 2008 11:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 23,089, Visits: 31,635
Check BOL (Books Online). The procedure you are looking for is msdb.dbo.sp_start_job (or in BOL, just sp_start_job).




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
Posted Monday, December 15, 2008 12:04 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Monday, December 15, 2008 12:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:16 AM
Points: 114, Visits: 1,065
Thank you both. Warm regards, Hope
Post #619880
Posted Monday, December 15, 2008 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:16 AM
Points: 114, Visits: 1,065
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
Posted Monday, December 15, 2008 1:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 23,089, Visits: 31,635
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




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 #619933
Posted Monday, December 15, 2008 1:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:16 AM
Points: 114, Visits: 1,065
Yes, thank you.
Post #619942
Posted Monday, December 15, 2008 2:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 11:16 AM
Points: 114, Visits: 1,065
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
Posted Monday, December 15, 2008 2:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:09 PM
Points: 23,089, Visits: 31,635
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.




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 #620017
Posted Thursday, December 27, 2012 5:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:28 AM
Points: 102, Visits: 1,050
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
Post #1400568
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse