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:
