I want to send dbmail showing the number of affected rows after an update sql agent job runs

Question

I have a SQL agent job that runs several UPDATE statements. What I want is to have it send of an email after it runs everyday. I get the emails but it doesn't show the number of rows affected or anything. Here is what I have.

--Print @tableHTML
DECLARE @eSubject varchar(250)
DECLARE @emailTo varchar(250)
SET @eSubject = 'Number of rows updated'   
SET @emailTo = 'me@me.com' 
EXEC msdb.dbo.sp_send_dbmail @recipients=@emailTo,
    @subject = @eSubject,
    @body = @@ROWCOUNT,
    @body_format = 'HTML';

This will kind of work but what I would rather have is

--Print @tableHTML
DECLARE @eSubject varchar(250)
DECLARE @emailTo varchar(250)
SET @eSubject = 'Number of rows updated'   
SET @emailTo = 'me@me.com' 
EXEC msdb.dbo.sp_send_dbmail @recipients=@emailTo,
    @subject = @eSubject,
    @body = @@ROWCOUNT + 'row(s) affected by UPDATE',
    @body_format = 'HTML';

However, i get syntax errors due to the '+' after @@ROWCOUNT.

Use an INT variable to to store the value of `@@ROWCOUNT` and this should be the very next statement after the query has executed and set that variable to `@body = @rcount` — M.Ali, Apr 04 '14 at 13:35

score 0 · Answer 1 · 2014-04-04T13:41:00.130

change this line

@body = @@ROWCOUNT + 'row(s) affected by UPDATE',

to be this

@body = CONVERT(varchar(10),@@ROWCOUNT) + 'row(s) affected by UPDATE',

the problem is mismatch of data types, therefore sql server does not know how to deal with + sign.

additionally @@ROWCOUNT only returns row count of last executed statement in your case it is SET statement. Thus it will always return 1;

Just add another variable and after select statement add SET @RowCountVariable = @@ROWCOUNT

after that in can modify

@body = CONVERT(varchar(10),@RowCountVariable)+ 'row(s) affected by UPDATE',

M.Ali · Accepted Answer · 2014-04-04T14:16:15.573

After any INSERT, UPDATE, DELETE or SELECT statement @@ROWCOUNT is populated for that statement.

Say you execute a statement

SELECT * FROM dbo.foo

SELECT @@ROWCOUNT   --<-- here it will show number of rows affected by select

now if you you even declare a variable and assign value to it

DECLARE @Test_Var INT = 1;

SELECT @@ROWCOUNT   --<-- here @@ROWCOUNT will show you 1

Therefore where ever you are in you code, onec you have executed a statement and you are interested in the number of rows being affected by that statement , make that statement followed by a variable to store the value of @@ROWCOUNT

DECLARE @rcount INT

SELECT * FROM dbo.foo
SELECT @rcount = @@ROWCOUNT;

Now you can use this variable anywhere . Do as vladimir has explained to show the message you want to show , but the value of @@ROWCOUNT at the point is saved in a variable safely.

Your Code

DECLARE @eSubject varchar(250)
DECLARE @emailTo varchar(250)
SET @eSubject = 'Number of rows updated' --<-- this would have reset the value of @@ROWCOUNT to 1
SET @emailTo = 'me@me.com'         --<-- this would have reset the value of @@ROWCOUNT to 1 again
EXEC msdb.dbo.sp_send_dbmail @recipients=@emailTo,
    @subject = @eSubject,
    @body = @@ROWCOUNT,
    @body_format = 'HTML';

Update

Your update statement

DECLARE @Rcount NVARCHAR(1000);
UPDATE foo
   SET Colfoo = 'foo'

 -- now use the variable to store the value

SET @Rcount = CAST(@@ROWCOUNT AS NVARCHAR(10)) + ' row(s) affected by UPDATE';

SELECT @Rcount;  --<-- test it

thank you! this is good info. I am however still getting a syntax error on this line: @body = CONVERT(VARCHAR(10),@rcount) + 'row(s) affected by UPDATE', SQL isn't liking the convert(varchar(10) piece. — BSanders, Apr 04 '14 at 14:11

I want to send dbmail showing the number of affected rows after an update sql agent job runs

2 Answers2