Sql-server – Send Multiple Attachments With Send DBMail

sql serversql-server-2008-r2t-sql

I thought that you just needed to separate two file names with a ; but my syntax fails with an error of

Msg 102, Level 15, State 1, Line 251
Incorrect syntax near '@filename2'.

This is my full syntax, what do I need to do in order to attach both files to my email?

Declare @filename1 varchar(max), @filename2 varchar(max)
Set @filename1 = 'C:\Testfiles\Test1.csv'
Set @filename2 = 'C:\Testfiles\Test2.csv'

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email', 
  @body = @body1,
  @file_attachments = @filename1;@filename2;

Best Answer

The semi-colon is a statement terminator, and you haven't correctly escaped your attachments. In other words, you've done this:

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email', 
  @body = @body1,
  @file_attachments = @filename1;

@filename2; -- Syntax Error

According to the MSDN article on sp_send_dbmail:

[ @file_attachments= ] 'file_attachments'

Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, Database Mail limits file attachments to 1 MB per file.

You need to do this:

Declare @filenames varchar(max)
Set @filenames = 'C:\Testfiles\Test1.csv;C:\Testfiles\Test2.csv'

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email', 
  @body = @body1,
  @file_attachments = @filenames;

If you want to programmatically add attachments, you would do something like this (notice how the second and third files begin with a semi-colon):

DECLARE @filenames varchar(max)
DECLARE @file1 VARCHAR(MAX) = 'C:\Testfiles\Test1.csv'
SELECT @filenames = @file1

-- Optional new attachments
DECLARE @file2 VARCHAR(MAX) = ';C:\Testfiles\Test2.csv'
DECLARE @file3 VARCHAR(MAX) = ';C:\Testfiles\Test3.csv'

-- Create list from optional files
SELECT @filenames = @file1 + @file2 + @file3

-- Send the email
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email', 
  @body = @body1,
  @file_attachments = @filenames;
Related Question