0

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.

  • 4
    I would simply add a call to sp_send_dbmail in the job step. Would that work for you? – spaghettidba Mar 17 '16 at 16:37
  • that's fine, I am struggling for getting the message into the @body parameter of sp_send_dbmail, including the affected rows from the dynamic script – Marcello Miorelli Mar 17 '16 at 16:41
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.