July 2, 2008 at 1:00 pm
I'm trying to get the sp_send_dbmail to work with this particular query I've written but I can't get around a little (maybe) issue.
Everything works fine if I run this:
EXEC msdb.dbo.sp_send_dbmail
@recipients= 'whatever@somethingorother.com',
@body= 'Hi There',
@query= 'use [databasename] select * from mcptasks',
@subject= 'KFP Needed for TAT';
But if I attempt to restrict the results by column name or value such as this:
EXEC msdb.dbo.sp_send_dbmail
@recipients= 'whatever@somethingorother.com',
@body= 'Hi There',
@query= 'use [databasename] select * from mcptasks where tas_id = '##'',
@subject= 'KFP Needed for TAT';
...it yells about incorrect syntax near '25'. Is there away to get it to take the quotes are run the query?
July 2, 2008 at 1:06 pm
First, assign the string to a variable. Then, use two-single-quotes where you want single-quotes in the string. Then use the variable in the parameter.
declare @Q varchar(1000)
select @Q = 'use [databasename] select * from mcptasks where tas_id = ''##'''
EXEC msdb.dbo.sp_send_dbmail
@recipients= 'whatever@somethingorother.com',
@body= 'Hi There',
@query= @Q,
@subject= 'KFP Needed for TAT';
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 1:40 pm
Thanks, GSquared. That worked perfectly.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply