There's also the option of using xp_logevent which has the benefit of not raising an error to the client.

-Mike

>From the BOL...
>
>Errors and messages, or events, are generated by Microsoft SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the application log and compares events to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert.
>
>By default, user-defined messages with severities less than 19 are not sent to the Windows application log when they occur and therefore do not trigger SQL Server Agent alerts.
>
>
>So in the stored procedure you can use a 'raiserror with log' statement to write to the windows log so the agent picks it up. Just make sure when you create the alert you use a distinct error message and your alert will pick up on.
>
>Hope this is enough to get started...
>
>Eric
>
>
>>I have a SP that checks datetime stamps on 12 tables. I am scheduling the SP to run every half hour. If the most recent datetime stamp is more than 30 minutes old I would like the SP to trigger an alert that would email me. How do I write a Stored Procedure that can do this?