sp_send_dbmail question

Question

Post reply

sp_send_dbmail question

Paresh Motiwala

SSCommitted

Points: 1772
More actions
July 30, 2014 at 10:25 am

#397140

I have this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA_Mail',
@recipients = 'paresh.motiwala@gmail.com',
@body = 'The count of the unprocessed records.',
@subject ='Number of unprocessed records on SQL1 '+ cast(@recordcount as varchar(10));
somehow the @subject...doesn't like the + sign.
The @recordcount does evaluate itself to proper number.
if I use @subject = @recordcount it works great.
any help is welcome..
Paresh
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

you cannot append values, or assign variables to the parameters in line:

--not allowed

@subject ='Number of unprocessed records on SQL1 ' + cast(@recordcount as varchar(10));

--the replacement

DECLARE @mysubject varchar(500) = 'Number of unprocessed records on SQL1 ' + cast(@recordcount as varchar(10));

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA_Mail',

@recipients = 'paresh.motiwala@gmail.com',

@body = 'The count of the unprocessed records.',

--assign the parameter to a variable that did the work

@subject = @mysubject

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 2

Thanks mate....I just did that and it worked.

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 3

Quick suggestion, if you are on SQL 2012 or later, use the concat function.

😎

DECLARE @recordcount INT = 007;

DECLARE @mysubject varchar(500) = CONCAT('Number of unprocessed records on SQL1 ',@recordcount);

SELECT @mysubject;