Post reply

Script to send an email based on contents of a job step's output file

  • September 30, 2008 at 3:13 pm

    #213510

    Comments posted to this topic are about the item Script to send an email based on contents of a job step's output file

  • May 9, 2013 at 10:48 am

    #1613923

    Excellent. I am adding this to my library. We had many problems with SQL Server based email because of the overhead, admittedly very small for one email. The problem is that our advertising email was also SQL based and what we needed to do was use a defined email server. So, using MSDN I created this script for SQL 2000 and updated later for later for 2005.

    CREATE PROCEDURE usr_sp_send_cdosysmail

    @From_Addr VARCHAR(500) ,

    @To_Addr VARCHAR(500) ,

    @Subject VARCHAR(500) ,

    @Body VARCHAR(8000) ,

    @SMTPserver VARCHAR(25) = 'localhost',

    @BodyType VARCHAR(10) = 'textbody'

    AS

    DECLARE @imsg INT

    DECLARE @hr INT

    DECLARE @source VARCHAR(255)

    DECLARE @description VARCHAR(500)

    EXEC @hr = sp_oacreate 'cdo.message', @imsg out

    EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

    EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @SMTPserver

    EXEC @hr = sp_oamethod @imsg, 'configuration.fields.UPDATE', NULL

    EXEC @hr = sp_oasetproperty @imsg, 'to', @To_Addr

    EXEC @hr = sp_oasetproperty @imsg, 'from', @From_Addr

    EXEC @hr = sp_oasetproperty @imsg, 'subject', @Subject

    -- If you are using html e-mail, use 'htmlbody' instead of 'textbody'.

    EXEC @hr = sp_oasetproperty @imsg, @BodyType, @Body

    EXEC @hr = sp_oamethod @imsg, 'send', NULL

    EXEC @hr = sp_oadestroy @imsg

  • May 2, 2016 at 1:13 pm

    #1876022

    Thanks for the script.

Viewing 3 posts - 1 through 2 (of 2 total)

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