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: Friday, October 17, 2014 2:03 PM
Points: 93, Visits: 156
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 @ 9:31 AM
Points: 12,962, Visits: 32,501
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: Friday, October 17, 2014 2:03 PM
Points: 93, Visits: 156
Thanks mate....I just did that and it worked.


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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 2,533, Visits: 7,110
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