Sending SQL Notifications with CDOSYS

  • A friend of mine took the Microsoft example proc and produced a more robust version. It defaults to using the servername from @@servername if one is not provided, and it can send file attachments:


    use

    master

    go

    CREATE

    PROCEDURE [dbo].[sp_sendmail_CDOSYS]

    @To

    varchar(100) ,

    @Subject

    varchar(100)=" ",

    @Body

    varchar(4000) =" ",

    @Importance

    int = 1, -- 0=low, 1=normal, 2=high

    @cc

    varchar(100)= " " ,

    @Bcc

    varchar(100)= " " ,

    @Attachments

    varchar(8000)=NULL, /* seperated by ; */

    @HTMLFormat

    int = 0,

    @From

    varchar(100)= null ,

    @Server

    varchar(255) = null,

    @UserName

    varchar(255) = null,

    @password

    varchar(255) = null

    /*********************************************************************
    Created by : Corey Embry
    Description: Use CDOSYS to Send Mail using smtp
    Base Code was obtained from MS Article Q312839
     
    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
    exec sp_sendmail_CDOSYS
    @To = 'Coreye@avalamarketing.com'
    , @Subject = 'CDOSYStest' -- Optional
    , @Body ='this is a test' -- Optional
    , @Importance int = 1 -- Optional
    , @cc = 'mickey@disney.com' -- Optional
    , @Bcc = 'Minnie@disney.com' -- Optional
    , @Attachments = 'c:\test.txt' -- Optional ; Delimited
    , @HTMLFormat = 0 -- Optional Default Text
    -- , @From = 'Coreye@avalamarketing.com' -- Optional
    , @Server='avalafs1' -- Optional
    -- , @UserName= -- Optional
    -- , @Password= -- Optional
    ***********************************************************************/

    AS

    SELECT @Server = isnull(@Server, @@SERVERNAME)

    Select @From = isnull(@From, @@SERVERNAME)

    Declare @iMsg int

    Declare @hr int

    Declare @int int

    Declare @Tmp varchar(8000)

    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', @Server

    if @UserName is not null and @password is not null

    begin

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value', @UserName

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value', @Password

    end

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

    EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc

    EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC

    /* if there are any attachments */

    if

    len(@Attachments)>0

    begin

    while len(@Attachments)>0

    begin

    set @int = charindex(';',@Attachments)

    if @int > 0

    begin

    set @Tmp = left(@Attachments,@int-1)

    set @Attachments = right(@Attachments,(len(@Attachments)-@int))

    end else

    begin

    set @Tmp = @Attachments

    set @Attachments = ''

    end /* Add the Attachment */

    Print @Tmp

    EXEC @hr= sp_OAMethod @iMsg, 'AddAttachment', NULL, @Tmp

    end

    end

     
     

    -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

    if

    @HTMLFormat <> 1

    begin

    EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

    end
    else
    begin

    EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

    end

    -- Save the configurations to the message object.

    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

    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

    Kindest Regards,

    Clayton

  • Comments posted to this topic are about the item Sending SQL Notifications with CDOSYS

  • This technique is an oldie but a goodie.

    One of Clinton Herring's contributions to "Script of the Day" a while back (I can't remember the date, but the script is dated 08/22/2001) does this too, and, might I add, is a bit more thorough than both examples posted above (Perhaps it gets overlooked because the script title is potentially misleading). 

    In any event, Clinton's version of this idea uses an OLE automation implementation of the CDOSYS dll, includes error handling, a help/usage message, a clever technique to verify attachments, and, IMHO, is very well-factored.

    In addition to any attachments you specify, Clinton's script can also perform a user-defined SQL Query (passed as a parameter to the SPROC), pipe the query results out to a text file (to the drive wih the most free space!), and attach the report to the email!  A great idea for automating server reports and emailing them to the DBA, or, generating application-specific reports and emailing them to your business stakeholders.

    See what you think!

    SQL 2K SMTP mail on Windows 2K

    http://www.sqlservercentral.com/scripts/contributions/510.asp

    Script Rating   Total number of votes [86]

    By: Clinton Herring

    SP_SQLSMTPMail is an OLE automation implementation of the CDOSYS dll for Windows 2000 which utilizes a network SMTP server rather than an Exchange server/Outlook client. The stored procedure functions similar to xp_sendmail including the ability to run a query and attach the results. No MAPI profile is required. It is also a working, detailed example of an OLE automation implementation. This update corrects a problem when the proc is called twice in the same batch without an intervening 'Go'. The cause is the sp_OAStop. It needs to be removed or commented out. The stated method of operation in the BOL is incorrect. 11/5/2002 Some people have reported errors when running this stored procedure. They have not been failures of the stored procedure. They are errors related to improper configuration/permissions for the SQL server to use the local network SMTP relay server for either internal or out going mail. 11/20/2002 Fixes a problem related to the OSQL call to send an attached query. OSQL was not releasing its lock on the first output file it created until the session ended, hence, calling the proc in a cursor or loop prevented subsequent query attachments. 04/09/2003 Comment correction.

    Best,

    Ken

  • Works great, just do not forget to remove the space in this line!

    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas. microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'

  • I recently saw your article at http://www.orcsweb.com/articles/sendmailsql.aspx and it solved my problem perfectly.

    Just wanted to say thanks.

    Windows 2008 Server | SQL Server 2008

  • I am using very simple VBscript to achieve the same thing, and attachements are allowed as well after small modification

    --------------------------------------

    Set oMsg = CreateObject("CDONTS.NewMail")

     oMsg.From = "abc"

     oMsg.To = "abc"

     oMsg.Subject = "you message"

     oMsg.Body = "you message"

     oMsg.Send

     Set oMsg = Nothing

    -------------------------------------

     

    Julia

  • Hi Clayton,

    I can't see how the Importance flag will work. You didn't call the method anywhere in the code?

    Regads,

    Andy

  • I like the suggestions, and the ability to attach a SQL query output to an email.

     

    I know the following works - EXEC ('master..xp_cmdshell ''isql /o' + @filename + ' /d' + @database + ' /Q"' + @sqlquery + '" /E''')

     

    But the only problem with the above is that you need SYSADMIN access to run XP_CMDSHELL

     

    I also do NOT want to use XP_SENDMAIL as it uses MAPI .

     

    Is there any other way to attach SQL queries to an email ??

  • Has anyone been able to make the "Importance" or "Priority" work using CDOSYS ?

  • I was using the sqlsmtpmail code and attaching a step to all of my jobs to exec the proc on failures.  Problem is, how can I put the Job Name and/or step name that failed in the subject and/or body of the emails?  I have everything else working.

     

    Thanks all!

  • Anyone?

  • double bump

  • I have also been unable to find an answer to this question. Can anyone answer this?

  • I would like to reccomend to not send email by port using CDOSYS in a stored proceedure. There are problems with doing this which may not manifest until right away. If you do so, you will probably run into an issue when setting one of the items in the configuration's fields collection (ie an access violation). Its best to send via pickup directory when calling CDOSYS via a stored proceedure. Another alternative is to put the codd into a vb script and to call via DTS. Over all sending using a pickup directory of a locally installed smtp server is better than sending via port: the proc code won't have to connect to a remote server, it will be more reliable (still works and is relaible if the networks or remote server has issues or is down) and will be much faster since the code can fire-and-forget (CDOSYS will just write the message file out to the local directory and your code is done). The SMTP server will handle things from there. I know many people don't want to install SMTP on a SQL server, however it does not take up many resources/processing time, its a system component and you get a much better and reliable configuration.

    A quick thing to remember for CDOSYS: 1 if by land (pickup) 2 if by sea (port)

    You may want to read my blog posting on issues with using CDOSYS under a stored proceedure.

    http://blogs.msdn.com/webdav_101/archive/2008/02/01/why-is-cdosys-failing-in-a-stored-proceedure.aspx

    I'll probably add more to the above blog entry later....

    Thanks,

    Dan

  • Thanks You!

    Works very well.

    Regards, Emito.:D

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply