question

AskSQLAlex avatar image
AskSQLAlex asked

Send Email from SQL DB with multiple tables in email body

Hi SQL Guru, Here is my question in detail. I need to send email from SQL Server with HTML table format that the email body will have several tables. 1. Query will be from database like SELECT Field1, Field2, Field3 FROM Table WHERE DateField>=Somedate 2. Field3 will be the factor to separate the tables, hence, the result will be grouped by Field3 values and put on separate tables 3. The email contnet will be more or less like the following Section: Field3 Value1 Field1 Field2 Field3 1 AA Value1 2 BB Value1 3 CC Value1 Section: Field3 Value2 Field1 Field2 Field3 1 OO Value2 2 XX Value2 Section: Field3 Value3 Field1 Field2 Field3 1 qwqw Value3 2 GGGG Value3 3 COCO Value3 4 KLMNO Value3 I have tried cursor, however, it will only return only one of the tables (based on the first FETCH value). Any help is appreciated
dbmailcursors
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Here's an example that uses 2 different queries/tables and formats them into HTML (I'm by no means any good at HTML, it took me long enough to get it looking like this!) declare @FirstQueryResults table (session_id smallint, database_id smallint); declare @SecondQueryResults table (object_id int, last_execution_time datetime, execution_count bigint); insert into @FirstQueryResults (session_id, database_id) select top 10 session_id, database_id from sys.dm_exec_sessions order by session_id desc; insert into @SecondQueryResults (object_id, last_execution_time, execution_count) select top 10 object_id, last_execution_time, execution_count from sys.dm_exec_procedure_stats; declare @HTML_Body varchar(max); declare @HTML_Head varchar(max); declare @HTML_Table1Header varchar(max); declare @HTML_Table2Header varchar(max); declare @EmailSubject varchar(255); set @HTML_Head = '' + char(13) + char(10) ; set @HTML_Head = @HTML_Head + ' ' + char(13) + char(10) ; set @HTML_Head = @HTML_Head + '' + char(13) + char(10) ; set @HTML_Head = @HTML_Head + '' + char(13) + char(10) ; set @HTML_Table1Header = ' ' + char(13) + char(10) ; set @HTML_Table1Header = @HTML_Table1Header + ' ' + char(13) + char(10) ; set @HTML_Table1Header = @HTML_Table1Header + ' Session ID' + char(13) + char(10) ; set @HTML_Table1Header = @HTML_Table1Header + ' Database ID' + char(13) + char(10) ; set @HTML_Table2Header = '


' + char(13) + char(10) ; set @HTML_Table2Header = @HTML_Table2Header + ' ' + char(13) + char(10) ; set @HTML_Table2Header = @HTML_Table2Header + ' Object ID' + char(13) + char(10) ; set @HTML_Table2Header = @HTML_Table2Header + ' Last Execution Time ' + char(13) + char(10) ; set @HTML_Table2Header = @HTML_Table2Header + ' Execution count' + char(13) + char(10) ; set @HTML_Table2Header = @HTML_Table2Header + ' ' + char(13) + char(10) ; set @HTML_Body = '' + @HTML_Head + 'Run from **insert some meaningful text here**' + char(13) + char(10) + 'Top 10 from sys.dm_exec_sessions ' + char(13) + char(10) + @HTML_Table1Header + ( select session_id as TD, database_id as TD from @FirstQueryResults for xml raw('tr') , elements ) + '

' + char(13) + char(10) + 'top 10 from sys.dm_exec_procedure_stats

' + char(13) + char(10) + @HTML_Table2Header + ( select object_id as TD, last_execution_time as TD, execution_count as TD from @SecondQueryResults for xml raw('tr') , elements ) + ''; set @EmailSubject = 'Your email subject here'; exec msdb.dbo.sp_send_dbmail @recipients = 'youremailaddress@somedomain.com', @subject = @EmailSubject, @body = @HTML_Body, @body_format = 'HTML', @profile_name = --you need to provide this; And the resulting email looks like ------------------------ ![alt text][1] [1]: /storage/temp/4493-2018-02-07-21h37-20.png

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.