SQLTeam.com | Weblogs | Forums

Mutiple Sheet Excel attachment - Database mail

sql2012

#1

Hi,

We have created a Job to sent mail with excel attachment. By fetching one of our table data into excel with some data ranges.
Now need some help to sent the email with multiple sheets excel attachment. Below have given my query part which us used currently with single sheet entry.

DECLARE @QUERY VARCHAR(MAX);
SET @QUERY = 'SET NOCOUNT ON SELECT * FROM dbo.TableName'
DECLARE @Subject NVARCHAR(MAX)
DECLARE @FILENAME NVARCHAR(MAX)

SET @Subject = 'Sending Mail With Excel Attachment'
SET @FILENAME='Excel Attachment.xlsx'

DECLARE @tab NCHAR(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail @profile_name='Tech Support',
@recipients='xxx@gmail.com',
@subject=@Subject ,
@body = 'Test Mail for Sending Excel Attachment',
@body_format ='HTML',
@query = @QUERY,
@query_result_separator = @tab,
@query_result_header = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = @FILENAME,
@query_result_no_padding= 1

Please give me a solution to fix this with a modified query for multiple sheet attachment.


#2

You are not actually sending an Excel spreadsheet - you are sending a tab delimited text file that can be opened with Excel. It is not possible to send an actual Excel spreadsheet using sp_send_dbmail directly from within SQL Server.

To get a native Excel format you need to use either SSIS or SSRS. I have found that SSRS is much better at creating Excel spreadsheets than using SSIS - and you can create your reports with specific grouping and paging that will create separate sheets for each group.