August 2, 2011 at 3:59 am
Griffster (8/2/2011)
STOP PRESS...it's working now! May be I hadn't updated the package after all! Thanks for your help.
Ah, glad that you found the issue.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 7:22 am
To throw out an answer to the original question, here's a utility proc I've used for a few years when I need to call other jobs and wait for completion. Wrote it myself, so no source disclaimers needed, use and modify as needed.
It's written as of SQL2005, appears to work on higher versions but not rigorously tested on anything higher.
CREATE PROCEDURE [dbo].[p_SQLAgent_ExecuteJobAndWait]
(@JobName varchar(1000),
@WaitTime varchar(8) = '00:00:05'
)
AS
SET NOCOUNT ON;
Declare @JobID UniqueIdentifier,
@ExecDT datetime,
@ExecD int,
@ExecT int,
@JobStatus int,
@ReturnCode int,
@ErrText varchar(200),
@ErrCode int,
@_WaitTime varchar(8),
@ExecDTNum bigint ;
SET @_WaitTime = @WaitTime;-- use local variable to avoid possible parm sniffing issue
SET @JobID = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name=@JobName);
IF Len(rtrim(ltrim(@JobName))) = 0
OR IsNull(convert(varchar(255),@JobID),'')=''
BEGIN
SET @ErrText = 'Job Name "' + @JobName + '" not valid';
RAISERROR (@ErrText, 16, 1);
RETURN;
END
SET @ExecDT = GETDATE();
-- date is a number formatted as yyyymmdd
SET @ExecD = (DATEPART(Year,@ExecDT) * 10000) +
(DATEPART(Month,@ExecDT) * 100) +
DATEPART(Day,@ExecDT);
-- time is a number formatted as hhmmss
SET @ExecT = (DATEPART(Hour,@ExecDT) * 10000) +
(DATEPART(Minute,@ExecDT) * 100) +
DATEPART(Second,@ExecDT);
SET @ExecDTNum = (CONVERT(bigint,@ExecD)*1000000) + @ExecT;
-- create a 5-second difference between the captured initial
-- time and the job's start time
WAITFOR DELAY '00:00:05';
EXEC msdb.dbo.sp_start_job @JobName;
SET @ErrCode = @@ERROR;
IF @ErrCode <> 0
BEGIN
SET @ErrText = 'Error ' + CONVERT(varchar,@ErrCode) + ' starting job "' + @JobName + '"';
RAISERROR (@ErrText, 16, 1);
END
SET @JobStatus = NULL; ;
WHILE (@JobStatus IS NULL)
BEGIN
WAITFOR DELAY @WaitTime;
/*
Job statuses:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
*/
SET @JobStatus = (SELECT TOP 1 run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = @JobID
AND ((CONVERT(bigint,run_date)*1000000) + run_time) > @ExecDTNum
AND step_id=0
ORDER BY run_date DESC, run_time DESC );
END
IF @JobStatus <> 1
BEGIN
SET @ErrText = 'Job failure on "' + @JobName + '"';
-- job didn't end successfully. 0 would indicate success.
RAISERROR(@ErrText,16,1);
END
August 3, 2011 at 7:13 am
Eurgh...I thought I'd cracked this one but it seems to be inconsistent. The last two times this has run in the daytime the send mail task has run before the sql agent job has completed. Could it be due to when the stored procedure is being run first after the sql agent job has been started?
I'm starting to think I may need to re-design this package and drop the Execute Sql agent job task (as it's too unreliable for programming in sequence) and use an execute sql task using one of the techniques here. This way, I can guarantee the WAIT command will be actioned. Would you agree?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply