SQLServerCentral Article

Sending Alerts Via a Custom SMTP procedure


The following article shows, through code, how to enable SQL Agent alerts and

using a simple stored procedure and DLL, send emails via an SMTP server rather

than using SQL Mail.  We will cover these items:

  • SQL Agent Event Alerts
  • SQL Agent Tokens
  • Calling DLL's via sp_OA methods


Here is the code for our simplecdo DLL (some items have been cut to

make the code easier to read), the routine is coded in VB and makes use of the

standard CDO library:

Public Function SendMessage(ByVal

ToAddress As String, _

    ByVal FromAddress As String, _

    ByVal SubjectText As String, _

    ByVal BodyText As String, _

    ByVal Server As String, _

    ByRef ErrorDescription As String) As Long

    'This is the original function (no attachments).


    Dim lngResult As Long

    lngResult = Send(ToAddress, FromAddress, SubjectText,

BodyText, Server, "", ErrorDescription)

    SendMessage = lngResult

End Function

Private Function Send(ByVal ToAddress As

String, _

    ByVal FromAddress As String, _

    ByVal SubjectText As String, _

    ByVal BodyText As String, _

    ByVal Server As String, _

    ByVal AttachmentFileName As String, _

    ByRef ErrorDescription As String)

    'Simple function for sending email from an SQL Server stored


    'Returns 0 if OK and 1 if FAILED.


    Dim Result As Long

    Dim Configuration As CDO.Configuration

    Dim Fields As ADODB.Fields

    Dim Message As CDO.Message

    On Error GoTo ERR_HANDLER

    'Initialise variables.

    Result = 0

    ErrorDescription = ""

    'Set the configuration.

    Set Configuration = New CDO.Configuration

    Set Fields = Configuration.Fields

    With Fields


= Server


= 25


= CdoSendUsing.cdoSendUsingPort


= CdoProtocolsAuthentication.cdoAnonymous


    End With

    'Create the message.

    Set Message = New CDO.Message

    With Message

        .To = ToAddress

        .From = FromAddress

        .Subject = SubjectText

        .TextBody = BodyText

        Set .Configuration = Configuration

        'Send the message.


    End With


        'Clean up objects.

        Set Configuration = Nothing

        Set Fields = Nothing

        Set Message = Nothing


        Send = Result

    Exit Function


        Result = Err.Number

        ErrorDescription = "Number [" &

Err.Number & "] Source [" & Err.Source & "] Description

        [" &  Err.Description & "]"

        Me.LastErrorDescription =



End Function

Copy the compiled DLL to your DB server and run

the command below to install:

regsvr32 simplecdo.dll

The Stored Procedure

The routine below makes the simple call to the SMTP email DLL.  We have

hard coded the IP (consider making it a parameter).  I was also sloppy with

the subject heading for the email, again this should be a parameter or

better still a SQL Agent Token (see later).


(@recipients varchar(200), @message varchar(2000)) AS

declare @object int, @hr int, @v_returnval varchar(1000), @serveraddress


set @serveraddress = '163.232.xxx.xxx'

exec @hr = sp_OACreate 'SimpleCDO.Message', @object OUT

exec @hr = sp_OAMethod @object, 'SendMessage', @v_returnval OUT, @recipients,

@recipients, 'test', @message, @serveraddress, @v_returnval

exec @hr = sp_OADestroy @object


Creating the Alert

Run Enterprise Manager, under the Management folder expand SQL Agent and

right click Alerts - New Alert.

In this case our alert is called CKTEST, we are going to send the DBA

an email whenever a logged severity 16 message occurs for any databases (no

really practical, but this is just an example).

Click on the Response tab next.

Uncheck the email, pager and net send options (where applicable

for your system).  Check the execute job checkbox, drop down the

list box and scroll to the top, and select <New Job>.

Enter the Name of the new SQL Agent Job, then press the Steps button

to create a step that will call our stored procedure.

Here we enter the step name, it is a t-sql script of course, and the command

which is:

exec master.dbo.usp_sendmail @recipients

= 'support@chriskempster.com', @message = '

Error: [A-ERR]

Severity: [A-SEV]

Date: [STRTDT]

Time: [STRTTM]

Database: [A-DBN]

Message: [A-MSG]

Check the [SRVR] SQL Server ErrorLog and the Application event log on the

server for additional details'

This is where the power of Agent Tokens comes into play.  The

tokens will be automatically filled in on the step running, there are

numerous tokens you can leverage off, but these are probably the best place to

start.  Here are some examples:

[A-DBN]           Alert

Database name


Alert Server name


Current Date  


Current Time 


Machine name

[SQLDIR]          SQL Server root


[STRTDT]         Job start time

[STRTTM]        Job end time

[LOGIN]           SQL login


[OSCMD]        Command line prefix


Instance name (blank if default instance)

Click OK twice and Job and its single step are now created.  In the

Response windows press Apply, then press OK to exit the Alert creation

window and return back to enterprise manager.

Goto Jobs under SQL Server agent to confirm the new Job and its step we have

just created.

Testing the Alert

Run Query Analyser and run the following:

RAISERROR ('Job id 1 expects the

default level of 10.', 16, 1) with log

The with log clause is important, the alert will not fire

without it.

Shortly later I receive the following email:


The routines discussed provide an excellent basis for moving back to event

alerts if you have not already done so, all without the need for SQL Mail. 

This provides you with a lot more flexibility, especially with the added

benefits of SQL Agent tokens, to really smarten up your daily monitoring.



VB Code for SMTP Routine, Donald Xie, 2001

"From SQLMail to SMTP in the SQL Agent", Bill Wunder, Published on

www.sswug.org, reference printed as at