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)
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
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
WHERE (AuditTrail.EnteredDate > GETDATE() - 1)
<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
SELECT HtmlData =
WHEN 0 THEN '<tr style="background-color: #F7F6F3">' + HtmlData + '</tr>'
ELSE '<tr>' + HtmlData + '</tr>'
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
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
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) = 'firstname.lastname@example.org',
@from varchar(255) = 'email@example.com',
@subject varchar(100) = 'My Example - '
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.