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

Rate

Share

Share

Rate