January 14, 2016 at 2:10 pm
Hi,
I am working to store the job output. I need an idea
I have a sql job with 12 steps. If it fails any step I want to store the output to a user table with the error message and at what step it failed?
If a step success it has to go to next step if it fails it has to quit reporting failure
or else
I am looking to add a logic to each step when it failed it needs to send email with step no and error msg. But here we are almost executing store procs for eachstep
Begin
BEGIN TRY
EXEC [dbo].[spTest]
END TRY
BEGIN CATCH
Declare @sub sysname = @@servername
DECLARE @subject nvarchar(max) = ''Job Failure Notification from '' + @sub;
DECLARE @body nvarchar(max) = '' Job Failed on stepno --''
+ CHAR(10) + CHAR(13) + ''Error Number: '' + CAST(ERROR_NUMBER() AS nvarchar(max))
+ CHAR(10) + CHAR(13) + ''Error Message: '' + ERROR_MESSAGE();
DECLARE @to nvarchar(max) = ''ramana3327@yahoo.com'';
DECLARE @profile_name sysname = ''SQLMailProfile'';
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
@recipients = @to, @subject = @subject, @body = @body;
END CATCH
END'
I selected on the success action it has to go to next step on failure need to report failure and quit
but it is sending email with error message but still it is going to the next step
January 14, 2016 at 3:02 pm
the reason your job continues to the next step after sending an email is because the step completes successfully (ie proc runs, error caught, email sent). the try-catch shelters the error from the the agent. try adding a throw to your catch clause.
Begin
BEGIN TRY
EXEC [dbo].[spTest]
END TRY
BEGIN CATCH
Declare @sub sysname = @@servername
DECLARE @subject nvarchar(max) = ''Job Failure Notification from '' + @sub;
DECLARE @body nvarchar(max) = '' Job Failed on stepno --''
+ CHAR(10) + CHAR(13) + ''Error Number: '' + CAST(ERROR_NUMBER() AS nvarchar(max))
+ CHAR(10) + CHAR(13) + ''Error Message: '' + ERROR_MESSAGE();
DECLARE @to nvarchar(max) = ''ramana3327@yahoo.com'';
DECLARE @profile_name sysname = ''SQLMailProfile'';
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
@recipients = @to, @subject = @subject, @body = @body;
THROW;
END CATCH
END'
January 15, 2016 at 12:25 am
So for each job step I need to include this job right?
How can I display step id where it failed just like @@servername
January 15, 2016 at 7:39 am
you could use msdb.dbo.sp_help_job @execution_status = 1 to identify job step. you would have to do some parsing of the output.
my suggestion would be to checkout the msdb.dbo.sysjobhistory table. You can remove the try-catch from every step and just run the proc. create a step that that queries sysjobhistory for failed jobs and emails the output. then have all other steps run that step on failure.
step 1 - run proc - on success: next step , on failure: email step[4]
step 2 - run proc - on success: next step, on failure: email step [4]
step 3 - run proc - on success: quit job reporting success, on failure: email step[4]
step 4 - email step - on success; quit job reporting failure, on failure; quit job reporting failure
here is an example query from sysjobhistory
SELECT step_name,step_id,sql_message_id, sql_severity, message, cast(cast(run_date as varchar(8)) as date)
FROM msdb.dbo.sysjobhistory
where run_status <> 1
and cast(cast(run_date as varchar(8)) as date) = cast(sysdatetime() as date)
and job_id = '<job_id>'
January 19, 2016 at 4:15 pm
Thank you.
Instead of sending the output through email
If you want to store each run step by step in user database table. How could you do that?
January 19, 2016 at 4:30 pm
ramana3327 (1/19/2016)
Thank you.Instead of sending the output through email
If you want to store each run step by step in user database table. How could you do that?
You could create a table to store the data you want about each step. In the procedure executed by each step, insert a row into the table. You could then query the table to return the output of a single run. If you want to track performance in the same table, add an EntryDate Datetime not null default GETDATE() column to the table. If you just wan to track performance and nothing else, use msdb.dbo.sysjobhistory like Bob suggested.
January 19, 2016 at 11:00 pm
The stored procedures can't be modify
I am executing a sp in each step. If any step failed either it is executing the sp or just adhoc query I need to write the output i.e. why it failed and in which step it failed in to the user database table instead of writing the output to the file or log to the table in msdb
Is it possible to do?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply