Trying to figure out how to send multiple file attachments with sp_send_dbmail

Question

Here's the scenario:

We have files stored in a varbinary(max) column.
I'd like to select these as individual files, along with the names of the files stored nearby, as individual file attachments.

Can this be done?

It's easy enough to send the results of a query as an attachment, but it just sends 1 file.

Answer 1

As @Weihui Guo points out, you can separate multiple files with a semi-colon. You can also have both an attachment via @query_attachment_filename and @file_attachments. In the code below, both produce a csv file and the email is generated with both as attachments.

DECLARE @cmd varchar(1000)
DECLARE @sql varchar(1000)

SET @cmd = 'sqlcmd -S localhost -d MYDB -E -o "C:\MyPath\SellVsBasePriceDetail.csv" -Q "select * from MYDB.dbo.vwSellVsBasePrice order by LocationDescription,Class" -W -w 999 -s","'
EXEC master..xp_cmdshell @cmd

SET @m_sql = 'select * from vwSellVsBasePriceSummary order by LocationDescription,Class'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'FOO DBMAIL',
@recipients = 'foo@foo.org', 
@subject = @m_subject,
@body = @m_msg,
@body_format ='HTML',
@query = @sql,
@execute_query_database = 'MYDB',
@attach_query_result_as_file=1,
@query_attachment_filename = 'SellVsBasePriceSummary.csv',
@query_result_separator = ',',
@query_result_header = 1,
@query_result_width = 32767,
@query_result_no_padding = 1,
@file_attachments = N'C:\MyPath\SellVsBasePriceDetail.csv';

Answer 2

I hope this answer may help others who use sp_send_dbmail to send multiple attachments. I have a sql server agent job to send the query result as an attachment, part of the reasons is that the attached txt file keeps the right format/grid. Here is the script:

IF (SELECT COUNT(itemnumber) FROM tblItems WHERE date = Convert(date,Convert(varchar(8), getdate(), 112))) > 0
-- add this condition so that when there is no query result, say over the weekend, your client won't receive an empty email.
BEGIN

 Declare @currentDate varchar(8), @currentDate2 varchar(10), @filename varchar(100), @subjecttxt varchar(100), @weekdayname varchar(10)
 Set @currentDate = Convert(varchar(8), getdate(), 112)
 Set @currentDate2 = Convert(varchar(10), getdate(), 101)
 Set @weekdayname = datename(dw, @currentDate)
 Set @filename = @currentDate + '.txt'
 Set @subjecttxt = 'Results for ' + @weekdayname + ' ' + @currentDate2    

 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'profile_name',
 @recipients = 'test@test.com',
 --@copy_recipients = 'test@test.com',
 @blind_copy_recipients = 'test@test.com',
 @query = 'exec storedprocedure',
 @subject = @subjecttxt,    
 @body = 'mail body.',
 @attach_query_result_as_file = 1,
 @query_attachment_filename = @filename;

END

A couple of months later, I was asked to add more attachments to the same email, which seems not possible to use the same sql server agent job. Here is my strategy as a workaround. Basically you'll need to add an additional step to run sqlcmd and output the file. But the step needs to be run as Operating system (CmdExec):

sqlcmd -S SQLServerName -dDatabaseName -E -Q "Stored Procedure Name" -o "C:\myFolder\myFile.txt"

Run this step first, then modify the Transact-SQL. You can also run sqlcmd as a batch file.

IF (SELECT COUNT(itemnumber) FROM tblItems WHERE date = Convert(date,Convert(varchar(8), getdate(), 112))) > 0
-- add this condition so that when there is no query result, say over the weekend, your client won't receive an empty email.
BEGIN

 Declare @currentDate varchar(8), @currentDate2 varchar(10), @filename varchar(100), @subjecttxt varchar(100), @weekdayname varchar(10), @file2 varchar(200)
 Set @currentDate = Convert(varchar(8), getdate(), 112)
 Set @currentDate2 = Convert(varchar(10), getdate(), 101)
 Set @weekdayname = datename(dw, @currentDate)
 Set @filename = @currentDate + '.txt'
 Set @file2 = 'C:\myFolder\myFile.txt'
 Set @subjecttxt = 'Results for ' + @weekdayname + ' ' + @currentDate2    

 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'profile_name',
 @recipients = 'test@test.com',
 --@copy_recipients = 'test@test.com',
 @blind_copy_recipients = 'test@test.com',
 @query = 'exec storedprocedure',
 @subject = @subjecttxt,    
 @body = 'mail body.',
 @attach_query_result_as_file = 1,
 @query_attachment_filename = @filename,
 @file_attachments = @file2;

END

Now you can send multiple attachments using your original job which only sends a single attachment.

Update: According to the documentation,[ @file_attachments = ] 'file_attachments' Is a semicolon-delimited list of file names to attach to the e-mail message, one can add multiple attachments by using a semicolon to separate the file names. Note that no space is allowed after the semicolon because DB mail will interpret that as part of the path name.

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'profile_name',
        @recipients = 'email@domain.com',
        @subject = 'subject',
        @body = 'body',
        @file_attachments = N'C:\Documents\attachment1.txt;C:\Documents\attachment2.txt';

Answer 3

Based on the docs, this can't be done. You can only attach multiple files when they attached from the file system (using @file_attachments=).

Query results are always attached as a single file.