Sending SQL Notifications via CDO.SYS
page 1 of 1
Published: 20 Oct 2004
Unedited - Community Contributed
Abstract
This article demonstrates how to create a stable, free, easy to configure means of sending SQL notifications without installing anything special on the server.
by Web Team at ORCS Web
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 11418/ 15

While SQL Server has a very mature set of tools, one thing that is lacking is the ability to send a simple email alert based on a pre-defined set of rules. At first glace, it appears that email notifications exist but what becomes a problem to many people is the requirement for a MAPI subsystem to be present on the server. This means that Outlook or an equivalent program has to be installed on the server. Not only that, to properly configure it, the administrator will need to log into the server as the user that the SQL Server service is running under. I have always found this inconvenient so I set out to create a stable, free, easy to configure means of accomplishing the same thing without installing anything special on the server. I've included steps below to complete this task successfully.

Before we start, an SMTP server needs to be available. This can be set up on the server itself, on another server within the local network, or outside the network. To install an SMTP service on the SQL Server, ensure that IIS SMTP Services are installed. You can do so from Add/Remove programs in the Control panel. Once it is installed, '127.0.0.1' will need to be added to 'relay' on the 'access' tab in the SMTP Properties. Alternately, an SMTP server of another mail server can be used instead. This information will need to be added to the 'sp_send_cdosys' stored procedure we'll create later.

First we need to create the stored procedure to send the e-mail. This can be done with Enterprise Manager and Query Analyzer. For our example, we'll implement the job with Enterprise manager using the Northwind database.

In Enterprise Manager, expand the Northwind database, select and right-click on 'stored procedures', and then select 'New Stored Procedure… '. Note: This can also be added to the Master database so that it is available for all databases.

screen shot 1

Delete the contents in the box, and copy and paste the contents below in the stored procedure box (everything between ' --- ').

Note: The sp_send_cdosysmail stored procedure uses ‘sp_OACreate' and ‘sp_OASetProperty' stored procedures; this will require the user account running it to have SA permissions.

CREATE PROCEDURE [dbo].[sp_send_cdosysmail] 
(
 @From varchar(100), 
 @To varchar(100), 
 @Subject varchar(100)=" ",
 @Body varchar(4000) =" " 
)


/****************************************** 


This stored procedure takes the parameters and sends 
an e-mail. All the mail configurations are hard-coded 
in the stored procedure. Comments are added to the 
stored procedure where necessary. References to the 
CDOSYS objects are at the following MSDN Web site: 
http://msdn.microsoft.com/library/default.asp
?url=/library/en-us/cdosys/html/_cdosys_messaging.asp 


*******************************************/ 
AS 


Declare @iMsg int 
Declare @hr int 
Declare @source varchar(255) 
Declare @description varchar(500) 
Declare @output varchar(1000) 


--***** Create the CDO.Message Object ***** 


EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT 


--*****Configuring the Message Object ***** 


-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp
--  ?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 
  'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
  '2' 


-- This is to configure the Server Name or IP address. 


-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 
  'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 
  'MailServerName' 


-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null


-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject


-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL


-- Sample error handling. 
IF @hr <> 0 
SELECT @hr
BEGIN
  EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
  IF @hr = 0
  BEGIN
    SELECT @output = '  Source: ' + @source
    PRINT  @output
    SELECT @output = '  Description: ' + @description
    PRINT  @output
  END
  ELSE
  BEGIN
    PRINT '  sp_OAGetErrorInfo failed.'
    RETURN
  END
END


-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO 

Once you have copied the stored procedure into the box, find the line below within the code.

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'

Replace 'MailServerName' with either 'localhost' or your 'SMTPMailServer'.

Be careful of word wrapping. When pasting from my example you may find that some lines wrap. You may need to double check the lines to make sure nothing wrapped that shouldn't have.

Next, as an example we will create a job that will backup the Northwind database, then send a notification via e-mail when it completes successfully.

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

screen shot 2

On the 'General' tab name the job 'Backup Northwind'.
On the 'Steps' tab, click on 'New', enter the information below, and then click OK. (The path should be a path that exists on the SQL Server.)

screen shot 3

This is what you should see after you click 'OK'. Notice that the 'On Success' column says 'Goto next step' (this is ok, because we are about to create another step) and the 'On Failure' column says 'Quit with failure' (this is ok, because if the job fails, we don't want it to go to the next step).

screen shot 4

Now we'll add the job step that will notify us that the job has completed.

Click 'New…' and add the information below (everything between ' --- ').

----------------------------------------


Name:     SendNotification
Database:  Northwind
Command:   EXEC sp_send_cdosysmail
'from@emailaddress.com*',  
'to@emailaddress.com*', 
'subject*', 
'I've added an example message for you*
Team,

The Northwind database has been backed up successfully.

Thank you,
ORCS Web '

----------------------------------------

*fill in the fields with the desired information.

screen shot 5

Click on the 'Advanced' tab and change the 'On Success' box to 'Quit the job reporting success' and then click 'OK'.

screen shot 6

This is what the job should look like now.

screen shot 7

For our purposes, the 'Schedules' and 'Notifications' tabs' default settings are fine; we will not need to make any changes. Click 'OK' and your job should appear in the job window.

screen shot 8

Now, right-click on the job we just created and click 'Start Job'.

screen shot 9

Make sure that the Job step named 'Backup' is selected then click start. When the job complete, you will be sent an email notification that looks like this.

screen shot 10

Now, even though email notifications can be setup as a 'job step' you can also setup an alert based on a SQL Server event that will send an email and include details of the event. Next we will setup an alert that will send an email notification with details.

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

screen shot 11

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'.

screen shot 12 ---------
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… '.

screen shot 13

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:

Use master
BACKUP DATABASE Northwind TO DISK = 'c:\temp\northwind.bak*'

*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.

screen shot 14

You can find more information about sending e-mail notifications at http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech
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.

by Desiree Harris, 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.



User Comments

Title: Error...   
Name: Robert
Date: 2008-10-29 2:10:58 AM
Comment:
By using your code I obtain the following error code:
-2147220982

Thank you
Title: Re: attachment in email   
Name: Desiree
Date: 2006-10-05 5:05:45 PM
Comment:
Hi Arindam,

Yes, you can add attachments by adding the code below to the stored procedure.

EXEC @sp_OAMethod @iMsg, 'AddAttachment', null, @attachment

~Desiree
Title: attachement in email   
Name: Arindam Gupta
Date: 2006-10-05 6:43:54 AM
Comment:
Hi,

I would liek to know if we can send attachments also using this. if so, pls tell how.

pls mail to ari_gupta@hotmail.com

Thanks.
Title: no need for sa   
Name: amartins
Date: 2006-08-01 7:34:00 PM
Comment:
the user that executes the job needs only to be on the sysadmin server role. so we can use other user to execute the sp.
but it's true, is a very good article, excellent
Title: RE: Nice aritcal   
Name: Desiree Harris
Date: 2006-03-16 2:44:57 PM
Comment:
Hi Ramakrishna,

When I did a google search based on the error message, I found many links that may help you get to the root of your issue. Try pasting your error message in google and try some of the suggestions recommended at a few of the links listed. I've listed one below for you. Good Luck!

- http://www.systemwebmail.com/faq/4.2.9.aspx

Desirée
Title: Nice aritcal   
Name: Ramakrishna
Date: 2006-03-16 3:32:05 AM
Comment:
Hi nice this artical. but when i copy past in my system and change of smtp server. it will be display below message in message box. pls explain wt the problem.


The message could not be sent to the SMTP server. The transport error code was 0x800ccc15. The server response was not available
Title: Re: Amazing Article   
Name: Desiree
Date: 2006-02-07 11:22:04 AM
Comment:
Hi Jojit,

Yes, you can embed images in an email using CDOSYS but, I haven't tried it myself. I did a quick google search and found the link below. Good luck!

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20869247.html

Desirée
Title: Re Amazing Article   
Name: Jojit
Date: 2006-02-07 5:35:52 AM
Comment:
Great article!!!

One question though... Is it possible to embed an image (jpg) using the same librabry (CDO)?

Thanks.
Title: Re: Amazing article!!   
Name: Desiree
Date: 2006-01-13 11:11:54 AM
Comment:
Hi Jeff,

You are quite welcome. I'm glad you found the article useful.

~Desiree
Title: Amazing article!!   
Name: jeff kelly
Date: 2006-01-12 5:05:17 PM
Comment:
Desiree,

Thanks so much for taking the time to make this article. I've tried it and it worked perfectly!!!

Your saving company's lots of money in design and testing... and I thank you again!!!

cheer,

jeff
Title: SQL Server Jobs   
Name: Desirée
Date: 2005-08-12 9:13:37 AM
Comment:
I didn't realize it but, there aren't many step-by-step articles on how to setup SQL Server jobs, which surprises me. I only found a couple articles that somewhat discuss SQL Server jobs but, neither give a step-by-step example nor discuss jobs in depth. I’ve included a few links below, but I’ll work on an article that will provide basic information on SQL Server jobs that will prove useful to many people.

http://www.computerperformance.co.uk/SQL2000/SQL_jobs_alerts.htm

http://www.databasejournal.com/features/mssql/article.php/10894_3489111_2

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=93

Desirée
Title: JOB SQL   
Name: Naveen
Date: 2005-08-11 3:00:07 AM
Comment:
Good...Please add some more examples to Add or Delete the job
Title: One of th emost amazingly helpful SQL Admin articles EVER   
Name: Dave
Date: 2005-03-17 11:44:04 AM
Comment:
I'm setting up a SQL Cluster, and can't use Outlook for MAPI client and notification. This article is the perfect solution, thanks for the awesome effort!






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 7:11:01 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search