Obtaining error message information in alerts and jobs

Question

Obtaining error message information in alerts and jobs

paksu

SSC Journeyman

Points: 84
More actions
March 3, 2005 at 2:51 pm

#164800

Hopefully, I can state the problem I'm having in an understandable way.
I'm running SQL Server 2K, sp3a.
I set up a trigger which does a RAISERROR to fire an alert. The alert runs a job. Everything works well so far.
Now, in the job step, I would like to get the actual message text of the error that originally fired the alert. The reason for this is that the message text contains a field (%s) which is substituted at error time with a value which can be used to change the behavior of the job.
In the BOL, in the "Automating a Response to an Alert" section there is a statement, "Creating jobs to respond to the alert is time-consuming because you must first parse and analyze the information in the message and then insert the relevant information into Transact-SQL commands," which implies this can be done, albeit slowly. Of course, the rest of the section refers to replication which is not involved in my situation. I have not been able to find any reference for this type of thing.
Any ideas?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Look in Books OnLine for sp_add_jobstep where the specifaction for the @command can include one or more of the case-sensitive tokens which are replaced at run time. These token are not availalbe under query analyzer but only is a job that has been invoked by an alert !

Be sure to wrap the tokens with double quotes as the brackets are part of the token and are also replaced. Here is an example of how to reference in a job step:

declare @ServerName varchar(255)

, @DBname varchar(255)

, @Error varchar(255)

, @Severity varchar(255)

, @ErrorMsg varchar(255)

select @ServerName = "[A-SVR]"

, @DBname = "[A-DBN]"

, @Error = "[A-ERR]"

, @Severity = "[A-SEV]"

, @ErrorMsg = "[A-MSG]"

SQL = Scarcely Qualifies as a Language

philcart SSC-Forever Points: 47796 More actions · Answer 2

There is a good article on SQL Agent tokens over at http://www.sqldev.net

--------------------
Colt 45 - the original point and click interface

paksu SSC Journeyman Points: 84 More actions · Answer 3

This is great! Just what I was looking for! Thanks guys. It worked like a charm.