Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»»

Sending scheduled SQL query results via HTML e-mail using SSIS Expand / Collapse
Author
Message
Posted Thursday, October 28, 2010 11:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:21 PM
Points: 12, Visits: 67
hi there,
i am also having problem generating the html. It seems to be working just fine but html data column are empty. for example there are 10 records, it is putting 10 empty rows in there instead of data filled.
Post #1012545
Posted Wednesday, November 10, 2010 8:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 12:18 PM
Points: 8, Visits: 50
Hey Jeff, I like your simplified T-SQL only solution a lot, I just used it for a small project. Here are a couple small tips for others trying to use the code Jeff posted, you need to specify body_format='HTML' for the send mail call, and you also need to add blanks to the end of all data that could be blank, so that grid lines are rendered in all versions of outlook/internet explorer (it will work in some versions without them). The thing is, you can't use a regular " " space, and you can't use " ", you have to use the alternate space generated by holding down the alt key, typing 255, then releasing the alt key. It looks the same as a regular space but is treated differently by outlook/internet explorer. The column width issues can drive you crazy because people running different machines with different versions of windows/outlook/internet explorer could potentially all see different formatting of the HTML table. If there are good solutions to this issues, please post them. I used OP's SSIS solution a while ago in another project and I can't remember now if that solution suffered from the same formatting problem?? Maybe that is the value added in the (albeit very complicated) SSIS version.

At any rate, here is my working version of the t-sql only solution previously posted (obviously you would have to specify your own table name and query to test:

DECLARE @Body NVARCHAR(MAX),
@Email NVARCHAR(300),
@CC NVARCHAR(300)
-- @FirstName NVARCHAR(15) --If you want to use the recipients name in the email
;
--===== You could get the email address and name from a table or hard code it below
--can use multiple email addresses separated with a ";"
select @EMail = 'myEmail@domain.com'
select @CC = 'someotheremail@domain.com'

--SELECT @FirstName = 'x',
-- FROM #MyEmailTable
-- WHERE ID = @SomeParameter
;
--===== Create the body of the message including a formatted HTML table of query results data
SELECT @Body =
------- Create the table and the table header. (Easily readable HTML)
--You could use a greeting here: <p>Dear ' + @FirstName + ',</p>

--Note: Your table column headings are defined below, you can add spaces ( ) to make the columns wider, but this will only work on certain versions of internet explorer / outlook, some renderers will ignore the spaces.
'
<html>
<body>
<p>The following table shows blah blah blah....</p>
<table border="1" cellspacing="0" style="text-align:center">
<caption>Coverage Policy Exceptions</caption>
<tr style="background-color:AliceBlue"><th>Exception Order</th><th> Exception_Name  </th><th>Plan_Name</th><th>Internal_Policy_Nm</th><th>Policy_Nm </th><th>  Policy_Nbr  </th><th>  Status  </th><th>  Drug_Nm  </th><th>  Indication  </th><th>  SubIndication  </th><th>  Prev_Internal_Policy_Nm  </th><th>  Prev_Policy_Nm  </th><th>  Prev_Policy_Nbr  </th><th>  Prev_Status  </th><th> Change_Date_Time </th><th>  Changed_By_ID  </th><th>  Changed_By_Name  </th>
</tr>
'

------- Create the rest of the table. Filled in from data in the table.
+ SPACE(8)
+ REPLACE(--This just indents each row to make rows in the HTML apparent and easy to read
CAST((SELECT td = Exception_Order, N'', --<td></td> = "data" element in a row
--You might want to format dates, or convert things to text as in the examples below:
--td = CONVERT(NCHAR(11), OpenToEnrollDate, 106), N'',
--td = CAST(XXX AS NVARCHAR(10)), N''
--You need to add something to the end of any column that could be blank if you want grid lines to appear in all versions of windows/internet explorer/outlook (some versions work fine, others remove grid lines around blank cells)
--Please note, this is NOT a regular space - hold down alt key, type 255 then release alt key, this creates something like a space. A regular space does NOT resolve the grid line problem for SOME versions of windows/internet explorer/outlook.
td = Exception_Name + ' ', N'',
td = [Plan_Name] + ' ', N'',
td = [Internal_Policy_Nm] + ' ', N'',
td = [Policy_Nm] + ' ', N'',
td = [Policy_Nbr] + ' ', N'',
td = [Status] + ' ', N'',
td = [Drug_Nm] + ' ', N'',
td = [Indication] + ' ', N'',
td = [SubIndication] + ' ', N'',
td = [Prev_Internal_Policy_Nm] + ' ', N'',
td = [Prev_Policy_Nm] + ' ', N'',
td = [Prev_Policy_Nbr] + ' ', N'',
td = [Prev_Status] + ' ', N'',
--A date with time can be formated as below
td = replace(convert(varchar(8), [Change_Dt], 10), '-', '/') + ' ' + substring(convert(varchar(20), [Change_Dt], 9), 13, 5) + ' ' + substring(convert(varchar(30), [Change_Dt], 9), 25, 2) + ' ', N'',
td = cast([Change_By] as varchar(6)) + ' ', N'',
td = [Change_By_Name] + ' ', N''
FROM SomeTableName --Add where clause and/or order by clause as needed
FOR XML PATH('tr'),TYPE --<tr></tr> = row encapsulation
)AS NVARCHAR(MAX))
,'</tr>','</tr>'+CHAR(10)+SPACE(8))
------- Finalize the HTML
+ '
</table>
</body>
</html>'
;
-- All set. Send the email. (google sp_send_dbmail if you need help setting it up)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SpecifyYourProfileNameHere',
@recipients = @EMail,
@copy_recipients = @CC,
@subject = 'Specify Email Subject here',
@body = @Body,
@body_format = 'HTML' --If you don't include this, you will see all HTML tags in the email and it will not render correctly
;
GO

Post #1018701
Posted Tuesday, September 13, 2011 6:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 7:44 AM
Points: 8, Visits: 19
Quoting the last sentense of the article:
"Deploy the package on your SSIS server, schedule it as desired and you're done."

How can we schedule the email so that it can be sent, let's say at 3.00 AM every day?
Also, how can we pass parameters, so that the query is executed with the date just before sending the emails.

Thanks,
Marco
Post #1174594
Posted Saturday, October 29, 2011 2:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 7:44 AM
Points: 8, Visits: 19
Great article. Thank you very much.

Could you please tell me how I can add a chart in the report so that I can send a chart besides the table

Thanks in advance,
Marco
Post #1197763
Posted Monday, October 31, 2011 5:07 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:19 AM
Points: 646, Visits: 1,854
marco.yandun (10/29/2011)
Could you please tell me how I can add a chart in the report so that I can send a chart besides the table


Just click the 'display as chart' option, which is right next to the 'make me a cappucino' check box.
Post #1197970
Posted Monday, October 31, 2011 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 7:44 AM
Points: 8, Visits: 19
Hey David,

Could you please clarify where I can find that option?

Thanks,
Marco
Post #1198168
Posted Tuesday, November 1, 2011 3:09 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:19 AM
Points: 646, Visits: 1,854
Sorry Marco...I was being sarcastic. Generating a chart in HTML is way outside of the scope of this article / discussion.

As a general guideline, I would advise that you make sure that you understand code before you use it - or else you're going to find yourself out of your depth.

Have you thought about reporting services perhaps, as a sensible way to get charts from SQL Server?
Post #1198499
Posted Tuesday, November 1, 2011 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 7:44 AM
Points: 8, Visits: 19
Yes David,

I thought of it. Using RS is an easy way to create charts. My problem is I need to create the report and send it by email automatically.
I already followed the steps as explained in this excellent article and managed to send the tabular report automatically using SQL Server Agent, but now the new requirement to send a chart besides the tabular report has been asked.

Any advice from you will be highly appreciated,

Marco
Post #1198735
Posted Tuesday, November 1, 2011 9:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:20 PM
Points: 34, Visits: 319
Marco,
You can setup a subscription in SSRS by clicking the "Subscriptions" tab. You can configure it to send an email.
Post #1198779
Posted Tuesday, November 1, 2011 6:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 7:44 AM
Points: 8, Visits: 19
Thank you very much Geoffrey for your advice,

Got another different problem now: The Object Datasources just disappear from my Visual Studio 2008 when opening a report. Do you know how can I get it back? last time I had to format my machine and got it for some months until yesterday that I had the same problem. Now I just have formatted it again, but this time, on my second time I edited my project, it just vanished again... this is making me crazy...

Thanks,
Marco
Post #1199049
« Prev Topic | Next Topic »

Add to briefcase «««56789»»»

Permissions Expand / Collapse