is it possible to pass the message from sp_add_alert to the job? 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.
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.