August 11, 2011 at 6:24 am
Dear All,
Can anybody tell me how to run jobs throught a T SQL , I want another job should run after successful running of previous job like
JOB1
after successfully finishing the first job then second job should run
JOB2
and so on..
Please help..
Regards
Ashok
August 11, 2011 at 6:37 am
Hi,
Check this:
http://msdn.microsoft.com/en-us/library/ms186757.aspx
That'll probably do the trick for you.
Thanks,
Simon
August 11, 2011 at 6:48 am
s_osborne2 (8/11/2011)
Hi,Check this:
http://msdn.microsoft.com/en-us/library/ms186757.aspx
That'll probably do the trick for you.
Thanks,
Simon
What Ashok's asking is how to start the second job only on successful completion of the first. sp_start_job will start the job but won't wait to see whether it completes. You can either put a step in the first job that will start the second, or you can poll the sysjobhistory table until the first job finishes.
John
August 11, 2011 at 6:51 am
Hi,
Thanks, sorry, i should have said the the job steps will need to be appropriately configured.
Simon
August 12, 2011 at 1:02 am
John Mitchell-245523 (8/11/2011)
s_osborne2 (8/11/2011)
Hi,Check this:
http://msdn.microsoft.com/en-us/library/ms186757.aspx
That'll probably do the trick for you.
Thanks,
Simon
What Ashok's asking is how to start the second job only on successful completion of the first. sp_start_job will start the job but won't wait to see whether it completes. You can either put a step in the first job that will start the second, or you can poll the sysjobhistory table until the first job finishes.
John
Hi,
This would not work for me since I cannot edit jobs , my jobs is only run three jobs in sequence and second job should start after successful completion of the first one. Please help me if sysjobhistory table can help here and how..
August 12, 2011 at 1:22 am
ashok.faridabad1984 (8/12/2011)
Please help me if sysjobhistory table can help here and how..
Yes, sysjobhistory can help. You can interrogate it to find out which jobs have run and when, which jobs are still running, and which jobs have failed or succeeded. Have a go at writing a query to do that and post back if there's anything you're not clear about.
John
August 12, 2011 at 2:18 am
Here is a stored procedure I wrote a little while ago to handle such an issue.
I takes 3 input parameters, first the job which you want to know the status of, then the job which should be started after successful completion of the first job and at last the delay how often you want to check.
I won't claim that it's a 100% perfect, but it does the job on my servers.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_job_chain](
@job_name sysname,
@next_job sysname,
@WaitTime datetime = '00:00:05', -- this is parameter for check frequency
@JobCompletionStatus int = NULL OUTPUT
)
AS
DECLARE @job_iduniqueidentifier
DECLARE @job_ownersysname
DECLARE @canceled_by varchar(200)
CREATE TABLE #xp_results (job_id uniqueidentifier NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
SELECT @job_id = job_id FROM msdb.dbo.sysjobs
WHERE name = @job_name
SELECT @job_owner = SUSER_SNAME()
WAITFOR DELAY @WaitTime
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
WHILE EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
BEGIN
WAITFOR DELAY @WaitTime
DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
END
SELECT @JobCompletionStatus = run_status , @canceled_by = LEFT(REPLACE(message ,'The job was stopped prior to completion by User ',''),
CHARINDEX ('.',REPLACE(message ,'The job was stopped prior to completion by User ',''),1) )
FROM msdb.dbo.sysjobhistory
WHERE job_id = @job_id
AND step_id = 0
IF @JobCompletionStatus = 1
EXEC sp_start_job @job_name = @next_job
ELSE IF @JobCompletionStatus = 3
RAISERROR (' Job %s job has been cancelled by user: %s ',16, 1, @job_name, @canceled_by ) WITH LOG
ELSE
BEGIN
RAISERROR ('[ERROR]:%s job is either failed or not in good state. Please check',16, 1, @job_name) WITH LOG
END
RETURN @JobCompletionStatus
Hope this helps
[font="Verdana"]Markus Bohse[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply