Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail question Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 10:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:00 AM
Points: 91, Visits: 151
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



Post #1597847
Posted Wednesday, July 30, 2014 10:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,918, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1597860
Posted Wednesday, July 30, 2014 11:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:00 AM
Points: 91, Visits: 151
Thanks mate....I just did that and it worked.


Post #1597875
Posted Wednesday, July 30, 2014 2:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 1,992, Visits: 5,192
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;[code="sql"]

Post #1597970
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse