Database Mail - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Database Mail

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources