Configuring SQL Server 2000 Notification with CDOSys

Jonathan Kehayias, October 28, 2008

SQL Server 2000 provides the ability to send email from SQL Server with SQL Mail. One drawback to this is that SQL Mail is MAPI based and requires a MAPI subsystem to be present on the server for it to be used. Since no MAPI subsystem is available natively on Windows Server 2000 or 2003, this means that it is necessary to install a MAPI client like Microsoft Outlook onto the SQL Server for SQL Mail to be used. An alternative to this is to use the Collaboration Data Objects (CDOSYS) in combination with SQL Server OLE Automation to create a TSQL stored procedure to handle sending emails.

This topic was originally covered by Microsoft in a KB Article titled "How to send e-mail without using SQL Mail in SQL Server"

http://support.microsoft.com/kb/312839

The following stored procedure is a modified version of the code provided in the above article, to allow for attachments to the messages being generated.

SQL Server 2000 CDOSys Email Procedure

 
/**********************************************************************
 * Name:        usp_Send_CDOSysMail
 * Author:      Microsoft / customized by: Jonathan Kehayias
 * Date:        12 April 2007
 * Database:    DBA_Data
 *
 * Purpose:
 * Creates a OLE Automation object to send an email using a SMTP Server
 * configured in the Parameters table of the DBA_Data database.  If an
 * attachment is specified, it will be added to the message.
 *
 * 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
 *
 * Changes
 **********************************************************************
 * No Changes
 *
 **********************************************************************/
ALTER PROCEDURE [dbo].[Send_CDOSysMail] 
   @From varchar(4000),
   @To varchar(4000),
   @Subject varchar(4000)=" ",
   @Body varchar(4000) =" ",
   @Attachment varchar(4000) = " "
 
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)
   Declare @mailserver varchar(255)
 
   SELECT @mailserver = 'smtp.domain.com'
 
--************* 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.
   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', @mailserver
 
-- 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
 
-- Check for Attachment specified and attach if necessary.
   IF @Attachment IS NOT NULL
	BEGIN
	   EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', null, @Attachment
	END
-- 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
   

Return to Top

To utilize this stored procedure, it is first necessary to change the @mailserver parameters value from smtp.domain.com to match the SMTP server address for your organization. Once this has been done, create the stored procedure in a database on your server. In order to use the OLE Automation routines, the calling user must be a member of the sysadmin role. Since the intent of this procedure is for use in generating automated notifications with the SQL Agent, this requirement should not be a problem since the SQL Agent Service account is a sysadmin by default. To test the stored procedure after you create it, change the @From and @To variables in the following code and execute it.

Testing the CDOSys Email Procedure

 
exec dbo.Send_CDOSysMail
   @From = 'sender@domain.com',
   @To = 'recipient@domain.com',
   @Subject = 'Test Message generated from SQL Server CDOSys',
   @Body = 'This is a test message from SQL Server CDOSys'
   

Return to Top


Since DBA's often manage many SQL Servers that are at different versions, it is often best when logic is version specific to use a wrapper stored procedure to abstract different methods of doing the same task between editions of SQL Server. In the case of sending email notifications, the method for SQL Server 2000 contained above may not be the preferred method for SQL Server 2005/2005 where Database Mail can be used natively. Since the editions have different abilities, it is easiest for code management to create a wrapper stored procedure like the following to be used by other TSQL code:

SendEmailNotification Stored Procedure

 
/**********************************************************************
 * Name:        SendEmailNotification
 * Author:      Jonathan Kehayias
 * Date:        28 October 2008
 * Database:    DBA_Data
 *
 * Purpose:
 * Creates a wrapper stored procedure for email generation from SQL
 * stored procedures.
 *
 * Changes
 **********************************************************************
 * No Changes
 *
 **********************************************************************/
CREATE PROCEDURE [dbo].[SendEmailNotification]
   @Subject varchar(4000),
   @Body varchar(4000),
   @Attachment varchar(4000) = " "
AS
 
DECLARE @From varchar(4000)
DECLARE @To varchar(4000)
 
SELECT  @From = '"SQLMonitor" <monitor@domain.com>',
		@To = '"SQL DBA" <sqldba@domain.com>'
 
EXECUTE [dbo].[Send_CDOSysMail] 
   @From
  ,@To
  ,@Subject
  ,@Body
  ,@Attachment
 
   

Return to Top


This is article is part of a series on Automating Common DBA tasks in SQL Server. To see the full series see: Automating Common DBA Tasks

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Oct 28, 2008