TSQL to output result set in an email in a tabular format without showing number of rows affected

Hello,

I was thinking of configuring an alert for our application for which the output is a resultset of a query displayed in an email. The alert was good but since it was a direct output of a query it was not properly formatted. I wanted the output in a tabularized result set format without showing the no of rows affected. And here it is shown below.

SET NOCOUNT ON

Declare @cnt int
declare @alertSubject varchar(100) = 'Subject here';
declare @msg varchar(MAX)
select @cnt=count(1) from Table where condition='this or that'

set @msg =
'
<table border=1>' +
'
<tr>
<th>Col1</th>
<th>Col2</th>
<th>Col3</th>
<th>Col..n</th>
</tr>
' +
CAST ( (
select td = cast(Col1 as varchar(40)),'',
td = cast(Col2 as varchar(40)),'',
td = cast(Col3 as varchar(40)),'',
td = cast(Col..n as varchar(40))
from Table where condition='this or that'
FOR XML PATH('tr'), ELEMENTS XSINIL, TYPE
) AS varchar(MAX) ) +
'</table>
'

if @cnt >=1
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'DBAGroup',
@recipients = 'DBAs@xyz.com',
@query_result_separator= ' ',
@query_result_no_padding= 1,
@body = @msg,
@body_format = 'HTML',
@importance = 'High',
@subject = @alertSubject;
end</pre>
<pre>

Hope this helps!

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , . Bookmark the permalink.

One Response to TSQL to output result set in an email in a tabular format without showing number of rows affected

  1. SQL Asset says:

    This is what I was looking for. You saved my time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s