Attach multiple files in sp_send_dbmail using path and filename as variable

Question

I would like to attach multiple files in sp_send dbmail but filename and path is variable as it changes. Below is my SQl script. After running below script I am getting only 1 file on mail as attachment but there are multiple files which starts with ERR as name.

Can someone please help me here?

declare @files table (Filename varchar(1000))
declare @filename varchar(500)
declare @foldername varchar(1000) = REPLACE(CONVERT(varchar(10), GETDATE()-1, 112), '/', '')
declare @folderpath varchar(500)
declare @filepath varchar(1000)

set @folderpath = 'xp_cmdshell ''dir E:\Batch\Error_Logs\'+@foldername+ ' /b'''

insert into @files exec (@folderpath)

SELECT @filename = Filename from @files where Filename like 'ERR_%'

set @filepath = 'E:\Batch\Error_Logs\'+@foldername+'\'+@filename

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Mail'
, @recipients = '[email protected]'
, @subject = 'sub'
, @body = 'pfa'
, @file_attachments = @filepath;

Surendra · Accepted Answer · 2017-06-02 16:02:38Z

1

According to Microsoft documentation on sp_send_mail, you can doing it with delimiting (separating) it with semi colons (;).

[ @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 can try to concatenate all the file names with semi colons.

answered Jun 2, 2017 at 16:02

Surendra

7215 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Attach multiple files in sp_send_dbmail using path and filename as variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related