SQL Server : sp_send_dbmail

Rahul Polaboina 181 Reputation points
2021-10-05T21:11:47.757+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,271 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. CathyJi-MSFT 21,121 Reputation points Microsoft Vendor
    2021-10-06T02:14:08.977+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 106K Reputation points MVP
    2021-10-06T21:24:19.597+00:00

    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?

    0 comments No comments

  2. CathyJi-MSFT 21,121 Reputation points Microsoft Vendor
    2021-10-07T07:12:14.35+00:00

    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.