Friday, June 3, 2022

How to Print Affected Row(s) for job run using SQL Server Agent

 To do this, you need to do the following:

DECLARE @AffectedRows AS INT

UPDATE testdb set col1 = 1 where col2 = '2'

SET @AffectedRows = @@ROWCOUNT 

PRINT 'Row(s) affected ' + CAST(@AffectedRows AS VARCHAR)

Which will write the following in your defined log file when your SQL Server Agent job is running.

Job 'TestJob' : Step 1, 'Update Test' : Began Executing 2022-06-03 16:21:16


Row(s) affected 6 [SQLSTATE 01000]


Do note that you cannot just do PRINT @@ROWCOUNT as PRINT will reset @ROWCOUNT value to 0. Therefore you need to assign @@ROWCOUNT to a variable first before you PRINT.


No comments: