Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
abliss
Starting Member
6 Posts |
Posted - 2011-05-22 : 20:13:23
|
Hello everyone, I have a job running Sunday evening after COB. This job will generate an email for various customers showing NEW ticket activity with us for the past week.
The HTML that I have for the job is as follows:
DECLARE @TableHTML NVARCHAR(MAX) ;
SET @tableHTML = N''<H3>Weekly New Opened Ticket Report</H3>'' + N''<table border="1" ALIGN="left" STYLE="margin-right:10 px;">'' + N''<tr><th>Tickets</th><th>Customer Name</th>'' + N''<th>Date Entered</th><th>Status</th>'' + N''<th>Description</th></tr>'' +
CAST ( ( SELECT td = Tickets, '''', td = Customer_Name, '''', td = Date_Entered, '''', td = Status, '''', td = Description FROM TicketsOpenedThisWeek FOR XML PATH(''tr''), TYPE ) AS NVARCHAR(MAX) ) + N''</table>'' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = ''TicketProfile'', @subject = ''New Opened Ticket Report'', @recipients = ''thecustomer@yahoo.com'', @execute_query_database = ''_tickets_db'', @body = @TableHTML, @body_format = ''HTML'' ;'
Pretty cookie cutter and it runs with no problem. However, there is a new report that they want appended to this html email. How would I add another table to this so I can display the results for multiple queries?
Thanks in advance for any help |
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2011-06-03 : 11:47:24
|
As any of the Yak masters here can attest, I'm no expert, but you should be able to just keep going within the @TableHTML variable, so... DECLARE @TableHTML NVARCHAR(MAX) ;
SET @tableHTML = N''<H3>Weekly New Opened Ticket Report</H3>'' + N''<table border="1" ALIGN="left" STYLE="margin-right:10 px;">'' (this starts the table)+ N''<tr><th>Tickets</th><th>Customer Name</th>'' + N''<th>Date Entered</th><th>Status</th>'' + N''<th>Description</th></tr>'' +
CAST ( ( SELECT td = Tickets, '''', td = Customer_Name, '''', td = Date_Entered, '''', td = Status, '''', td = Description FROM TicketsOpenedThisWeek FOR XML PATH(''tr''), TYPE ) AS NVARCHAR(MAX) ) + N''</table>'' (this ends the table);
So just keep going inside the @TableHTML, like this: DECLARE @TableHTML NVARCHAR(MAX) ; SET @tableHTML = N''<H3>Weekly New Opened Ticket Report</H3>'' + N''<table border="1" ALIGN="left" STYLE="margin-right:10 px;">'' + N''<tr><th>Tickets</th><th>Customer Name</th>'' + N''<th>Date Entered</th><th>Status</th>'' + N''<th>Description</th></tr>'' +
CAST ( ( SELECT td = Tickets, '''', td = Customer_Name, '''', td = Date_Entered, '''', td = Status, '''', td = Description FROM TicketsOpenedThisWeek FOR XML PATH(''tr''), TYPE ) AS NVARCHAR(MAX) ) + N''</table>'' + N''<H3>New Heading 3</H3>'' + N''<table border="1" ALIGN="left" STYLE="margin-right:10 px;">'' N''<tr><th>Col1 Header</th><th>Col2 Header</th>'' + N''<th>col3 Header</th><th>Col4 Header</th>'' + CAST ( ( SELECT td = col1, '''', td = col2, '''', td = col3, '''', td = col4, '''', td = col5 FROM [your table or view] WHERE [your criteria] FOR XML PATH(''tr''), TYPE ) AS NVARCHAR(MAX) ) + N''</table>''; EXEC msdb.dbo.sp_send_dbmail @profile_name = ''TicketProfile'', @subject = ''New Opened Ticket Report and new report '', @recipients = ''thecustomer@yahoo.com'', @execute_query_database = ''_tickets_db'', @body = @TableHTML, @body_format = ''HTML'' ;'
Please let me know if this works, I'm new at this, too.
---------------- -Stephen |
 |
|
|
|
|
|
|