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