Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sending Alerts Via a Custom SMTP procedure

By Chris Kempster,

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
  • Using RAISEERROR

The SMTP DLL

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 procedure.
    '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
        .Item(CDO.CdoConfiguration.cdoSMTPServer) = Server
        .Item(CDO.CdoConfiguration.cdoSMTPServerPort) = 25
        .Item(CDO.CdoConfiguration.cdoSendUsingMethod) = CdoSendUsing.cdoSendUsingPort
        .Item(CDO.CdoConfiguration.cdoSMTPAuthenticate) = CdoProtocolsAuthentication.cdoAnonymous
        .Update
    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.
        .Send
    End With

    EXIT_FUNCTION:

        'Clean up objects.
        Set Configuration = Nothing
        Set Fields = Nothing
        Set Message = Nothing
   
        Send = Result

    Exit Function
        ERR_HANDLER:

        Result = Err.Number
        ErrorDescription = "Number [" & Err.Number & "] Source [" & Err.Source & "] Description
        [" &  Err.Description & "]"
        Me.LastErrorDescription = ErrorDescription

    GoTo EXIT_FUNCTION

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

CREATE PROCEDURE usp_sendmail (@recipients varchar(200), @message varchar(2000)) AS

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

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
GO

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
[A-SVR]            Alert Server name
[DATE]             Current Date  
[TIME]              Current Time 
[MACH]            Machine name
[SQLDIR]          SQL Server root directory 
[STRTDT]         Job start time
[STRTTM]        Job end time
[LOGIN]           SQL login ID
[OSCMD]        Command line prefix
[INST]              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:

Conclusion

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.

www.chriskempster.com

References

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 8/1/2004.

Total article views: 14316 | Views in the last 30 days: 5
 
Related Articles
FORUM

Passing alert message to my Job

alert message to job

FORUM

Setting up alerts for specific error messages

Setting up alerts for specific error messages

FORUM

can you please write some messages with this string "Timeout Expired" to sqlagent log

can you please write some messages with this string "Timeout Expired" to sqlagent log

ARTICLE

SQL Server Alerts

SQL Server Alerts provide a great way for the server to notify a DBA that some event has occurred, u...

FORUM

How to receive a Message from a MSMQ using Message Queue task with out message label as "String Message"

Receive message from a MSMQ using message queue task with lable other than "String Message".

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones