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!