One of the most common tasks for me is to send data from a query as the body of an e-mail. You can do this in SSIS by outputting a query to a flat file, but there's no easy way to format the content. You have the option of using XML and XSLT transformations, but that is a bit onerous for simple use cases.
This article is about how to produce a table like the one below in an e-mail using just SQL. The only real complexity is formatting alternate rows, which was my goal. I wanted it to look like a .NET DataGrid (shown below).
The basic approach involves three steps:
- Create a query that pulls together the data as HTML.
- Use bcp and xp_cmdshell to write the SQL to a file.
- Email the file using Blat, a free SMTP client (www.blat.net)
EDIT: Steps #2 and #3 are actually a bit redundant. You can send the HTML in an email using sp_send_dbmail. After creating the stored procedure below, procHtmlSql, you can send the SQL result by executing this SQL:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'MYSERVER Email', @recipients = 'foo@foo.com', @subject = 'The Email Subject', @query =N'EXECUTE MYDB.dbo.procHtmlSql', @attach_query_result_as_file = 0, @body_format = 'HTML';
One of the problems I ran into is the use of a <script> tag in the email, so there I had to abandon CSS styling and instead do inline styling on every row.
In order to find the odd/even rows, you need the ROW_NUMBER() function in a CTE, then use a CASE statement, and the modulo operator, to format the result:
CREATE PROCEDURE [dbo].[procHtmlSql] AS BEGIN SET NOCOUNT ON; WITH htmlresult AS ( SELECT ROW_NUMBER() OVER (ORDER BY AuditTrail.EnteredDate) AS RowNumber, '<td>' + AuditTrail.OldValue + '</td><td>' + AuditTrail.NewValue + '</td><td>' + AuditTrail.LoginID + '</td><td>' + CAST((CONVERT(smalldatetime, AuditTrail.EnteredDate)) AS VARCHAR(20)) + '</td>' AS HtmlData FROM AuditTrail WHERE (AuditTrail.EnteredDate > GETDATE() - 1) ) SELECT '<html><head><title></title></head> <body><br><br><table><tr style="background-color: #5D7B9D; font-weight: bold; color: white;"> <td>Old Value</td><td>New Value</td><td>Login</td><td>Entered Date</td></tr>' AS HtmlData UNION ALL SELECT HtmlData = CASE RowNumber%2 WHEN 0 THEN '<tr style="background-color: #F7F6F3">' + HtmlData + '</tr>' ELSE '<tr>' + HtmlData + '</tr>' END FROM htmlresult UNION ALL SELECT '</table></body></html>' END
Once you have the SQL with HTML formatting, you want to use that within another stored procedure to write the result to a file. This is what the second proc should look like:
CREATE PROC [dbo].[procHtmlFile] AS BEGIN DECLARE @strCommand varchar(255) DECLARE @lret int SET @strCommand = 'bcp "EXECUTE MYDB.dbo.procHtmlSql" QUERYOUT C:\MyProject\SqlHtml.html -T -c' EXEC @lRet = master..xp_cmdshell @strCommand, NO_OUTPUT END
Now you should be able to open the file "C:\MyProject\SqlHtml.html" in a browser and it should look similar to a .NET DataGrid.
Finally, send the file as the body of an e-mail with this procedure.
CREATE PROCEDURE [dbo].[procBlatSendMail] @to varchar(255) = 'foo@foo.com', @from varchar(255) = 'foo@foo.com', @subject varchar(100) = 'My Example - ' AS DECLARE @command as varchar(1500) SET @command = '' SET @command = @command + 'Blat C:\MyProject\SqlHtml.html' SET @command = @command + ' -to ' + '"' + @to + '"' SET @command = @command + ' -f ' + '"' + @from + '"' SET @command = @command + ' -subject ' + '"' + @subject + CONVERT(nvarchar(25), GETDATE()) + '"' EXEC master.dbo.xp_cmdshell @command
Simple, effective, and professional looking.
