SQL alert for a stored procedure?

Question

I have a SQL 2005 setup and am rather new :) Been cracking at this for a few hours and I just need some help.

I have been able to setup alerts successfully for the standard "SQL server performance events", its fun. So I already have email alerts working. However I need the alert thing to run a stored procedure I have created, and depending on its output it will alert me or not via email.

So far I have been trying to use the WMI events, but I keep getting an error "The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax"

the query definitely works so I have no idea.. is there a different way to do this?

Answer 1

You can use Management Studio or the sp_add_alert system stored procedure to add an alert and specify a job to execute on firing. In Management Studio, when you view the properties of the Alert, it is on the "Response" page. That job can consist of a single step calling your stored procedure. In your stored procedure, you can use the sp_send_dbmail system stored procedure to send an email based on logic you define.

From Books Online:

• sp_add_alert
• sp_send_dbmail