Add attachment sql server mail
To add an attachment to a SQL Server mail, you can use the sp_send_dbmail
stored procedure and specify the attachment using the @file_attachments
parameter.
Here is an example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile',
@recipients = '[email protected]',
@subject = 'Test Email with Attachment',
@body = 'This is a test email with an attachment.',
@file_attachments = 'C:\Path\To\Your\File.txt'
In this example, @file_attachments
is a table-valued parameter that accepts a list of file paths and names. You can specify multiple attachments by separating them with commas.
If you want to attach a file from a database table, you can use the @file_attachments
parameter with a SELECT statement that returns the file paths and names. For example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile',
@recipients = '[email protected]',
@subject = 'Test Email with Attachment',
@body = 'This is a test email with an attachment.',
@file_attachments = (
SELECT 'C:\Path\To\Your\File1.txt' AS file
UNION ALL
SELECT 'C:\Path\To\Your\File2.txt' AS file
)
Make sure to replace C:\Path\To\Your\File.txt
with the actual path and name of the file you want to attach.
Also, you can use @file_attachments
with a variable, for example:
DECLARE @file_attachments TABLE (file NVARCHAR(255));
INSERT INTO @file_attachments (file)
VALUES ('C:\Path\To\Your\File1.txt'), ('C:\Path\To\Your\File2.txt');
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile',
@recipients = '[email protected]',
@subject = 'Test Email with Attachment',
@body = 'This is a test email with an attachment.',
@file_attachments = @file_attachments
Make sure to replace C:\Path\To\Your\File.txt
with the actual path and name of the file you want to attach.
Please note that the file path and name should be in the format of C:\Path\To\Your\File.txt
and not C:/Path/To/Your/File.txt
Also, you can use @file_attachments
with a table variable, for example:
DECLARE @file_attachments TABLE (file NVARCHAR(255));
INSERT INTO @file_attachments (file)
VALUES ('C:\Path\To\Your\File1.txt'), ('C:\Path\To\Your\File2.txt');
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile',
@recipients = '[email protected]',
@subject = 'Test Email with Attachment',
@body = 'This is a test email with an attachment.',
@file_attachments = @file_attachments
Make sure to replace C:\Path\To\Your\File.txt
with the actual path and name of the file you want to attach.
Please note that the file path and name should be in the format of C:\Path\To\Your\File.txt
and not C:/Path/To/Your/File.txt
Also, you can use @file_attachments
with a table-valued parameter, for example:
CREATE TYPE dbo.FileAttachments AS TABLE (file NVARCHAR(255));
DECLARE @file_attachments dbo.FileAttachments;
INSERT INTO @file_attachments (file)
VALUES ('C:\Path\To\Your\File1.txt'), ('C:\Path\To\Your\File2.txt');
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Your Mail Profile',
@recipients = '[email protected]',
@subject = 'Test Email with Attachment',
@body = 'This is a test email with an attachment.',
@file_attachments = @file_attachments
Make sure to replace C:\Path\To\Your\File.txt
with the actual path and name of the file you want to attach.
Please note that the file path and name should be in the format of C:\Path\To\Your\File.txt
and not C:/Path/To/Your/File.txt