March 3, 2005 at 2:51 pm
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
March 3, 2005 at 3:11 pm
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
March 3, 2005 at 3:19 pm
There is a good article on SQL Agent tokens over at http://www.sqldev.net
--------------------
Colt 45 - the original point and click interface
March 3, 2005 at 3:56 pm
This is great! Just what I was looking for! Thanks guys. It worked like a charm.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply