Sending SQL Server Notifications with CDOSYS

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 the steps below to complete this task successfully.

Before we start, a 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, a 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.

Next, 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.

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.

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