January 27, 2017 at 1:06 pm
Hi,
Does anyone knows a handy stored procedure to execute a sql agent job with return result of the sql agent job?
I have to add few logic here to check sql job history checks and based on the output of the SQL agent job, I would like my result. Please let me know if possible.
Below is my syntax.
CREATE PROC [dbo].[SPXYZ]
WITH EXECUTE AS 'dbo'
AS
Begin
Begin Try
EXEC msdb.dbo.sp_start_job N'ABCSQLAgentJob';
End Try
Begin Catch
PRINT 'Full Backup failed. Please call On call DBA Support to resolve this issue.'
End Catch
End
January 27, 2017 at 1:25 pm
hello_san - Friday, January 27, 2017 1:06 PMHi,Does anyone knows a handy stored procedure to execute a sql agent job with return result of the sql agent job?
I have to add few logic here to check sql job history checks and based on the output of the SQL agent job, I would like my result. Please let me know if possible.Below is my syntax.
CREATE PROC [dbo].[SPXYZ]
WITH EXECUTE AS 'dbo'
AS
Begin
Begin Try
EXEC msdb.dbo.sp_start_job N'ABCSQLAgentJob';
End Try
Begin Catch
PRINT 'Full Backup failed. Please call On call DBA Support to resolve this issue.'
End Catch
End
I don't think this approach is going to work. sp_start_job is successful if the job starts. It's unsuccessful if the job doesn't start. The return code from sp_start_job isn't related to the outcome of the job. One option is that jobs themselves are aware of the job outcome and have notifications for how you want to handle whatever outcome.
Sue
January 30, 2017 at 1:47 am
This was removed by the editor as SPAM
January 30, 2017 at 2:41 am
You could check for the status of the job after running:IF (SELECT TOP 1 run_status FROM msdb.dbo.sysjobhistory WHERE job_id = '756E4E19-E6CC-4874-A7D7-B3EBE44A09D4' AND step_name = '(Job outcome)' ORDER BY run_date DESC) = 0 BEGIN
PRINT ' The job failed :(';
END
As the others have pointed out, the return is simply is the job started or didn't. it doesn't tell you if the job was then successful.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2017 at 8:05 am
I had this snippet I used a while back, which may be of use to you. It gets the ID of the job based on the name and then starts the job. It then has a while loop to check the sysjobactivity table to check if the job has completed
DECLARE @ErrorLogID INT,
@cnt INT,
@job_id UNIQUEIDENTIFIER,
@max INT,
@name NVARCHAR(128),
@session_id VARCHAR(32),
@start_date VARCHAR(32),
@stop_date VARCHAR(32)
BEGIN TRY
SET @name = 'NameOfJob' --JobName
SET @max-2 = 60 --loops through 60 * 2 second default = 2 Min max on job
SELECT @job_id = job_id
FROM msdb.dbo.sysjobs
WHERE name = @name
IF @name IS NOT NULL
BEGIN
IF @Debug = 1
BEGIN
PRINT '@name = ' + @name
PRINT '@job_id = ' + CONVERT(varchar(255), @job_id)
END
EXEC msdb.dbo.sp_start_job
@job_id = @job_id
SET @cnt = 0
WHILE @cnt < @max-2
BEGIN
SET @cnt = @cnt + 1
SELECT @session_id = CONVERT(varchar(32), session_id),
@start_date = CONVERT(varchar(32), start_execution_date, 120),
@stop_date = CONVERT(varchar(32), stop_execution_date, 120)
FROM msdb.dbo.sysjobactivity
WHERE job_id = @job_id
IF @stop_date IS NOT NULL
BEGIN
SELECT 1;
BREAK
END
IF @Debug = 1
BEGIN
PRINT CONVERT(varchar, GETDATE(), 120) + ' ' + @name + ' running (' +
'session=' + ISNULL(@session_id, '') + ' ' +
'start=' + ISNULL(@start_date, '') + ' ' +
'end=' + ISNULL(@stop_date, '') + ')'
END
WAITFOR DELAY '00:00:02'
END -- WHILE @cnt < 100
IF @Debug = 1
BEGIN
PRINT CONVERT(varchar, GETDATE(), 120) + ' ' + @name + ' completed (' +
'session=' + ISNULL(@session_id, '') + ' ' +
'start=' + ISNULL(@start_date, '') + ' ' +
'end=' + ISNULL(@stop_date, '') + ')'
END
END -- IF @name IS NOT NULL
END TRY
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply