Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

sp_add_alert stored procedure can react to different system messages and execute a job in response. It also can notify a person with the message text by email, pager of net send.

But how do I pass the sysmessage message (corresponding to the event that caused the alert) not to a person but to the job that is executed in response to the alert?

Let's consider this message:

select [description] from sysmessages where msglangid = 1033 and error = 829

This will yeild:

Database ID %d, Page %S_PGID is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

I'd like to receive this message in the job, so the job knows what %d and %S_PGID caused a problem.

share|improve this question
    
Are you talking about sysmessages or message column in msdb.dbo.sysjobhistory ? Please clarify what you mean by how do I pass the message (that caused the alert) not to a person but to the job that is executed in response to the alert? –  Kin Jul 30 '13 at 20:55
    
@Kin I'm talking about sysmessages. –  zespri Jul 30 '13 at 20:59
    
@Kin it's difficult for me to say what it is unclear for you, but let me try my best in explaining. In SQL an alert is caused by an event corresponding to a parametrized message listed in sysmessages. When alert happens two things can be done: a job started and a notification to an operator sent. The latter can include the message text. I can't find a way for the former to do the same. Please feel free to ask more questions if they help you better understand mine. –  zespri Jul 30 '13 at 21:37

1 Answer 1

up vote 1 down vote accepted

This article gives the answer. Basically the message is accessible by using SQL Server Agent Tokens and Macros.

In particular this Job Step that is calling a stored procedure worked for me nicely:

exec ProcessMyAlert '$(ESCAPE_SQUOTE(A-MSG))'

Note that I had to

right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens.

As per the linked article. In addition I had to restart SQL Server Agent so that this setting come into effect - it did not work until I restarted it, the article does not mention the restart however.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.