Sending SQL Server Notifications with CDOSYS

In Enterprise Manager, open 'Management' à 'SQL Server Agent', and then select and right-click on 'Jobs', and select 'New Job… '.

On the 'General' tab name the job 'SQL Server Severity Alert Notification'.

On the 'Steps' tab, click on 'New', name it 'SendEmail' and enter the script below (everything between '--').On the 'Advanced' tab, change the 'on success action' to 'Quit the job reporting success' and click 'OK'.

DECLARE @msg nvarchar(4000)
Set @msg = REPLACE("Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG] ", "'", "") --'
 

EXEC sp_send_cdosysmail 'from@emailaddress.com*',
'to@emailaddress.com*',
'subject*',
@msg
 

*fill in fields with desired information

I guess you're wondering what the script does. The script takes advantage of the power of agent tokens, which will be automatically filled in at runtime. You can get more information about agent tokens here:  http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm

The 'Schedules' and 'Notifications' tab don't need any changes at this time; let's click 'OK' again to complete the job.

Next we'll create the alert that will run the job we just created when an event occurs. In Enterprise Manager, open 'Management' à 'SQL Server Agent', and then select and right-click on 'Alerts', and select 'New Alert… '.

On the 'General' tab name the job 'SQL Server Severity Alert'. We'll leave the severity level at 010 as it will return information and isn't specific to an error and we'll let this alert fire for all databases.

On the 'Response' tab, put a check in the box for 'Execute job', select the job we just created, 'SQL Server Severity Notification', and then click 'OK'.

Now, we are ready to test. Let's backup the Northwind database in Query Analyzer with the script below:

*ensure the path exists on the SQL Server

This task will execute the alert. At this point, an email should appear in the inbox of the email address you specified in the script. I've included an example below.

You can find more information about sending e-mail notifications at http://support.microsoft.com/default.aspx?scid=kb;EN-US;312839

You can read more about agent tokens here http://www.sqldev.net/sqlagent/SQLAgentStepTokens.htm

You can read more about severity levels here http://www.developer.com/db/article.php/724711

We've seen here how to setup email alerts on the server without being required to install the Outlook client email program or another MAPI program. Windows 2000 Server and greater already has CDOSYS installed on it. If installing this on Windows NT, replace the stored procedure with one that uses CDONTS instead.

Desiree Harris is a support specialist with ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.