SQL Server – How to Send Email with Job Results

database-maildynamic-sqljobssql serversql server 2014

I have the following update,
that creates a table backup in a database called tablebackups with the records that are going to be updated, and then do 2 different updates.

            USE SAStockLevel
            GO

            DECLARE @SQL VARCHAR(max)

            SET @SQL = 


            '
            CREATE TABLE TableBackups.dbo._KK_'
    +CONVERT(VARCHAR(8),GETDATE(),112)
    +'_tblStockThreshold
                (
                  [strItemNo] [varchar](20) COLLATE Latin1_General_CI_AS
                                            NOT NULL ,
                  [sintMarketID] [int] NOT NULL ,
                  [sintChannelID] [int] NOT NULL ,
                  [lngQtyThreshold] [int] NOT NULL ,
                  [dtmRecordAdded] [datetime] NOT NULL ,
                  [strAddedBy] [varchar](50) COLLATE Latin1_General_CI_AS
                                             NOT NULL ,
                  [dtmLastUpdated] [datetime] NOT NULL ,
                  [strUpdatedBy] [varchar](50) COLLATE Latin1_General_CI_AS
                                               NOT NULL ,
                  [ActiveRecord] [bit] NOT NULL ,
                  [ActiveMarket] [bit] NOT NULL ,
                  [ActiveChannel] [bit] NOT NULL
            )

            -- UK

            UPDATE  dbo.tblStockThreshold
            SET     lngQtyThreshold = 0
            OUTPUT  DELETED.strItemNo, 
        DELETED.sintMarketID, 
        DELETED.sintChannelID, 
        DELETED.lngQtyThreshold, 
        DELETED.dtmRecordAdded, 
        DELETED.strAddedBy, 
        DELETED.dtmLastUpdated, 
        DELETED.strUpdatedBy, 
        DELETED.ActiveRecord, 
        DELETED.ActiveMarket, 
        DELETED.ActiveChannel
                    INTO TableBackups.dbo._KK_'
    +CONVERT(VARCHAR(8),GETDATE(),112)
    +'_tblStockThreshold
            WHERE   strItemNo IN (
                    SELECT  strItemNo
                    FROM    dbo.tblOrgGrpStockLevel
                    WHERE   lngTotal - ( lngAllocated + lngReserved ) 
    BETWEEN 1 AND 4
                            AND tintOrgGrpId = 1
                            AND lngTotal <> 0 )
                    AND sintMarketID = 1
                    AND sintChannelID IN ( 1, 2 )
                    AND lngQtyThreshold < 6666
                    AND lngQtyThreshold <> 0

             PRINT ''UK - NUMBER OF ROWS UPDATED '' + CAST ( @@ROWCOUNT AS VARCHAR) 

            -- US

            UPDATE  dbo.tblStockThreshold
            SET     lngQtyThreshold = 0
            OUTPUT  DELETED.strItemNo, 
        DELETED.sintMarketID, 
        DELETED.sintChannelID, 
        DELETED.lngQtyThreshold, 
        DELETED.dtmRecordAdded, 
        DELETED.strAddedBy, 
        DELETED.dtmLastUpdated, 
        DELETED.strUpdatedBy, 
        DELETED.ActiveRecord, 
        DELETED.ActiveMarket, 
        DELETED.ActiveChannel
                    INTO TableBackups.dbo._KK_'
    +CONVERT(VARCHAR(8),GETDATE(),112)
    +'_tblStockThreshold
            WHERE   strItemNo IN (
                    SELECT  strItemNo
                    FROM    dbo.tblOrgGrpStockLevel
                    WHERE   lngTotal - ( lngAllocated + lngReserved ) 
    BETWEEN 1 AND 4
                            AND tintOrgGrpId = 2
                            AND lngTotal <> 0 )
                    AND sintMarketID = 2
                    AND sintChannelID IN ( 1, 2 )
                    AND lngQtyThreshold < 6666
                    AND lngQtyThreshold <> 0

             PRINT ''US - NUMBER OF ROWS UPDATED '' 
    + CAST ( @@ROWCOUNT AS VARCHAR)

            '

            BEGIN TRY 

                  BEGIN TRANSACTION T1

                  EXEC (@SQL)

                     print 'the update has been run'
                     COMMIT TRANSACTION T1

            END TRY
            BEGIN CATCH



                print 'the update has failed'

                PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
                          'THE ERROR NUMBER:' 
    + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') 
+ CHAR(13) 

                    PRINT 'SEVERITY: '        
    + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') 
+ CHAR(13) +
                          'STATE: '           
    + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  
+ CHAR(13) 

                    PRINT 'PROCEDURE: '       
    + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') 
+ CHAR(13) +
                          'LINE NUMBER: '     
    + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  
+ CHAR(13) 

                    PRINT 'ERROR MESSAGE: '
                    PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)



                    WHILE @@TRANCOUNT > 0
                          ROLLBACK

            END CATCH

That is running fine, as you can see on the picture below.

enter image description here

question:

How can I send an email with the message below (from the job history)?

Message Executed as user: SqlService. UK – NUMBER OF ROWS UPDATED
27916 [SQLSTATE 01000] (Message 0) US – NUMBER OF ROWS UPDATED 39606
[SQLSTATE 01000] (Message 0) the update has been run [SQLSTATE 01000]
(Message 0). The step succeeded.

Best Answer

I got it working maybe not the best but it is ok for now.

what I did is, I have added the following code to my script:

    declare @sub varchar(150)
    select @sub = 
'-- Number of rows affected by threshold updates job run at ' 
+ CAST(convert(datetime, getdate(), 100)AS VARCHAR)
    --print @sub


        EXEC msdb.dbo.sp_send_dbmail 
            @recipients='marcelo.miorelli@foo.co.uk', 
-- change mail address accordingly
            @subject = @sub,
            @profile_name = 'MailProfile', -- Change profile name accordingly
            @body_format = 'text',
            @query= '


    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET NOCOUNT ON

    declare @table varchar(108)
    DECLARE @SQL VARCHAR(MAX)

    select @table = ''TableBackups.dbo._KK_''
+CONVERT(VARCHAR(8),GETDATE(),112)+
''_tblStockThreshold''

    SELECT @SQL = ''

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    select [MARKET]=COALESCE(sintMarketID,0),
           [x]=''''--'''',
           ROWS_UPDATED= COALESCE(COUNT (*) ,0)
    from '' +@TABLE + ''
    GROUP BY sintMarketID
    ORDER BY sintMarketID
    ''

    EXEC(@SQL)

    '

and then I get a result like the one you can see on the picture below:

enter image description here

Related Question