October 20, 2009 at 1:14 pm
I need to use sp_send_dbmail to send an email message.
The way I have it setup is a sproc runs every X minutes, looks for a certain type of problem that it finds via a SELECT statement.
If that problem is encountered it then sends an email message and needs to include the contents of that select statement.
The initial select statement dumps its results into a @TableVariable.
Is there a way I can take the results and pass those results to sp_send_dbmail's body text?
I don't want to tell sp_send_dbmail to execute a NEW query and send those results, I want to use the results I already stored in my table variable.
Thanks!
October 20, 2009 at 1:45 pm
The documentation on sp_send_dbmail includes rules on attaching query results. If that won't do for what you need, what I often do is turn the query results into an HTML string.
Looks like this:
create table #T (
ID int identity primary key,
Col1 varchar(100),
Col2 varchar(100));
insert into #T (Col1, Col2)
select 'Grid 1:1', 'Grid 1:2' union all
select 'Grid 2:1', 'Grid 2:2';
declare @BodyGrid varchar(max);
select @BodyGrid = coalesce(
@BodyGrid
+ '<tr><td>' + cast(ID as varchar(100))
+ '</td><td>' + Col1
+ '</td><td>' + Col2
+ '<tr>',
'<tr><td>' + cast(ID as varchar(100))
+ '</td><td>' + Col1
+ '</td><td>' + Col2
+ '<tr>')
from #T;
select @BodyGrid = '<table border="1">' + @BodyGrid + '</table>';
select @BodyGrid;
- 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
October 20, 2009 at 1:53 pm
Makes sense, thanks!
The issue with using the query attachment option is that if I do that it executes the query in a new session so I wouldn't have access to any of the @tableVariables I was using so I'd have to rerun all my "do I need to actually send this email" queries all over again which would seem to be wasteful.
I was using that SELECT COALESCE trick to build a comma separated string and just send that, but the HTML is a far superior presentation format.
Thanks!
October 20, 2009 at 1:59 pm
You're welcome.
You can get pretty fancy with it pretty easily. It's easy to add column headers (just add them as the top row of the grid when you add the table definition). You can use case statements to add font changes. And so on.
- 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply