question

RahulPolaboina-6019 avatar image
0 Votes"
RahulPolaboina-6019 asked Cathyji-msft commented

SQL Server : sp_send_dbmail

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

sql-server-generalsql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Before you even start to think about browsing the folder, test if you can sent a single named file from that network share, because that alone can be a battle. We tried this at my client the other day, but we concluded in our case that we lost the game.

And make the test from the context where you intend to call sp_send_dbmail. That is, if you plan to do this in an Agent job, test with an Agent job.

0 Votes 0 ·

I am able to send a single file successfully through an email by hardcoding it

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog RahulPolaboina-6019 ·

So what is the context under which this will execute? What is the architecture of the application?

I ask, because this will involve both Windows and SQL Server permission, and if you overlook security you will run into a brick wall.

Also, what does "SELECT @@version" report?

0 Votes 0 ·
Show more comments
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered RahulPolaboina-6019 commented

Hi @RahulPolaboina-6019,

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.

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

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.




· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your response, Is there anyway to dynamically frame the @filenames variable in above code, because each day new files will be added to the folder.

Till the folder path , the value is same,the filename changes on day to day basis

0 Votes 0 ·
Cathyji-msft avatar image Cathyji-msft RahulPolaboina-6019 ·

Hi @RahulPolaboina-6019,

Did files names store in a SQL table ? Or are there any rules for these files naming?

0 Votes 0 ·

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

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft commented

Hi @RahulPolaboina-6019,

@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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RahulPolaboina-6019,

Any update for this thread? Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·