June 30, 2006 at 11:39 am
Hey guys, I've been working on a notification job for a bit and can't seem to figure something out.
I got a script that looks like this
declare @sql varchar(500), @CardCount varchar(10), @sRecipients varchar (255), @sSubject varchar (1024)
SET @sRecipients = 'sqlserver@domain.com';
SET @sSubject = 'TestCount';
SET @sql = 'select count(''TypeCd'') from table1 where TypeCd = ''AIR'' and column2 >= 500'
exec (@sql)
EXEC master.dbo.xp_sendmail @sRecipients, @sql, @subject=@sSubject
When I get the email, The body contains:
select count(''TypeCd'') from table1 where TypeCd = ''AIR'' and column2 >= 500
how do I get it to give me the count instead of select statement?
Thanks.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
June 30, 2006 at 12:05 pm
declare @sRecipients varchar (255), @sSubject varchar (1024)
Declare @CountResult As int
Declare @EmailBody As varchar(500)
SET @sRecipients = 'sqlserver@domain.com';
SET @sSubject = 'TestCount';
SELECT @CountResult = count(TypeCd)
from table1 where TypeCd = 'AIR' and column2 >= 500
SELECT @EmailBody= 'Count result is ' + cast(@CountResult as varchar)
EXEC master.dbo.xp_sendmail @sRecipients, @EmailBody, @subject=@sSubject
June 30, 2006 at 12:12 pm
I was trying out ..:
exec (@sql)
SELECT CONVERT(int, @sql)
SET @CardCount = @sql
But yours did the trick.
Thanks PW.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply