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

  • RML51

    SSCarpal Tunnel

    Points: 4410

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

  • Dave Vroman

    SSC Eights!

    Points: 821

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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