T-SQL: How send mail + dynamic attachments from specific folder

https://msdnshared.blob.core.windows.net/media/2016/05/0640_NinjaAwardTinyGold.png  Award in March 2019

 

Introduction

Many times customer requirement to send list files send mail to customer.This is possible by SSIS and Tsql.
I am explain using Tsql methods.
 First you need check you are able send mail from mssql server.

you have keep following points ;

Prohibited file extensions:

Database Mail maintains a list of prohibited file extensions. Users cannot attach files with an extension that appears in the list. You can change this list by using sysmail_configure_sp.

Folder acces:

Database Mail runs under the SQL Server Engine service account. To attach a file from a folder to an email, the SQL Server engine account should have permissions to access the folder with the file.

Permission:

To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database.

Attachment size governor:

Database Mail enforces a configurable limit on the attachment file size. You can change this limit by using the sysmail_configure_spstored procedure.
 The default is 1,000,000 bytes which is 1MB. The maximum value we can use 2,147,483,647, which is roughly 2GB. If we try to use a value greater than 2147483647 it will throw an error. So change the value to the necessary size for your needs.

sample email format:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA',
    @recipients='dba@solutions.com',
        @subject = 'sample mail',
        @body =' Testing mail with attachment'

Code:

Declare @filenames varchar(max)
declare @body1 varchar(max)='This is a test email'
 
DECLARE @Path nvarchar(500)
  
SET @Path = 'c:\TEMP'    ---change folder here
 
DECLARE @FindFile TABLE
(FileNames nvarchar(500) 
,depth int ,isFile int) 
 
INSERT INTO  @FindFile 
EXEC xp_DirTree @Path,1,1 
 
 
--SELECT FileNames 
--FROM @FindFile WHERE isFile=1
 
 
IF EXISTS (SELECT 1 FROM @FindFile WHERE isFile=1)
BEGIN
 SELECT @filenames=STUFF((SELECT ';'+@Path+'\'+FileNames FROM @FindFile WHERE isFile=1 FOR XML PATH('')),1,1,'')
 
 --print @filenames
 
 EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'DBA',
  @from_address = 'dba@dba.com',
  @recipients= 'custumer@gmail.com',
  @subject= 'Test Email with  attachment', 
  @body = @body1,
  @file_attachments = @filenames;
 
END

Email format :

Track attachment history:

see also

• send mail from TSQL

---