January 16, 2014 at 5:27 pm
Whenever the job fails, usually we receive an generic email from SQL Database Mail telling us that certain step failed within a job. In order for us to find out what the cause of the failure was, we usually go to the Agent, look at the job history, and then step failed to see the exact cause of this. Is there a way to have email deliver this message as well, so we don't have to go and dig around so much every time?
January 16, 2014 at 11:22 pm
No easy way! But yes, you can do it programatically by pulling data from system tables. Create stored procedure to send mail and add stored procedure to the additional step if job fails.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 17, 2014 at 10:09 am
I sorta figured that out on my own. The only thing I'm having issues with figuring out is how to capture correct job id in order to pass the error message into a parameter.
January 17, 2014 at 10:45 am
SQL Server Agent recognizes a variety of 'tokens', which it will replace at runtime with the appropriate value. 'JOBID' is one of those tokens.
In some cases, you must add some text to the token, for it to function properly.
As an example, the tokens $(ESCAPE_NONE(STRTDT)) and $(ESCAPE_NONE(STRTTM)) return the date and time the Job was started.
You could use the 'JOBID' token as the parameter value to your stored procedure. The parameter datatype would, of course, need to be uniqueidentifier. Off-hand, I think you would need to place the token in single quotes, denoting a text string, so the Agent's replacement would be a valid string parameter.
The token(s) must be visible to SQL Server Agent; i.e. as a parameter to a stored procedure, or as a command-line argument. You cannot use them inside a stored procedure or SQL Script, as the Agent would not be able to "see" them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply