SQL Server : sp_send_dbmail

Question

I am trying to send email through SQL Server using "sp_send_dbmail" stored proc

For file_ Attachments parameters I need to browse a network folder and attach all the files under it

Example : \Idss\idss\IDSS\IDSS\Development\Process1 , in Process1 folder there are 5 files and all files should be sent as an attachment when a mail is sent.

Looking for the code for the file_ Attachments parameter

Answer 1

Hi @Rahul Polaboina ,

Please try below T-SQL;

Declare @filenames varchar(max)  
Set @filenames = '\\Testfiles\Test1.csv;\\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;  

Or

DECLARE @filenames varchar(max)  
DECLARE @file1 VARCHAR(MAX) = '\\Testfiles\Test1.csv'  
SELECT @filenames = @file1  
  
-- Optional new attachments  
DECLARE @file2 VARCHAR(MAX) = ';\\Testfiles\Test2.csv'  
DECLARE @file3 VARCHAR(MAX) = ';\\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;  

Quote from MS document.

[ @Gaydamak _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.

Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 2

To get a list of the files you can try:

SELECT * FROM sys.dm_os_enumerate_filesystem(' \\Idss\idss\IDSS\IDSS\Development\Process1', '*.*')

This will give you a list of the files in the folder. But I am not wholly sure that it works with a shared folder.

You say that you need to send the files to someone everyday. But if you need to send the files yourself personally, why involve SQL Server?

Answer 3

Hi @Rahul Polaboina ,

>@CathyJi-MSFT unfortunately no, Filenames should be the name of the file placed in the folder, they are not stored anywhere else

Please try below T-SQL;

------Find all files in the shared folder and insert the file name in one table  

use testnode3  
CREATE TABLE fileList (FileName VARCHAR(100),depth int,fil int);  

INSERT INTO fileList  
EXEC xp_dirtree '\\Testfiles\', 1, 1  

select FileName from fileList   

create Table fileList2(FileName2 VARCHAR(100))  

INSERT INTO fileList2   
Select '\\Testfiles\'+FileName from fileList  

select FileName2 from fileList2   

------Send DB Mail with multi file attachments  

Declare @filenames varchar(max)  
SELECT @filenames = STUFF((  
            SELECT ';' + FileName2  
            FROM fileList2   
            FOR XML PATH('')  
            ), 1, 1, '')  

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;  

------Drop the temporary tables  

Drop table dbo.fileList  
Drop table dbo.fileList2  

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.