In a previous post (SQL Database Mail - Send Emails with Attachment), we described how to attach a file to an email. There is a known bug related to this. Under certain conditions, the mail attachment will not be sent.
If a mail with an attachment is sent from within a transaction in snapshot isolation mode, the mail will be sent correctly, but the attachment is not there.
When it occurs:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT -- BEGIN TRANSACTION execute msdb..sp_send_dbmail @profile_name = 'profile name', @recipients = 'firstname.lastname@example.org', @subject = 'This is a nice subject', @body_format = 'TEXT', @body = 'I have a nice body too', @file_attachments = '\\path\to\sample.xls' -- COMMIT TRANSACTION
When you uncomment "BEGIN TRANSACTION" and "COMMIT TRANSACTION", the output is still "Mail queued", the mail is sent but without the attachment.
This issue is solved as part of Sql Server 2011. We don’t know yet if it will be back ported to previous versions.
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm