Problem sending an attachment using dbo.sp_send_dbmail

Question

I am trying to send an attachment using dbo.sp_send_dbmail. I can't do this I get the error Attachment file \VMName\ListTEAM\LCPP_CP.pdf is invalid.

Just some quick things that I have checked. I have tried various naming conventions. I have shared the folder that the attachment is in. I checked the Log On As in the Services MMC on the VM that the SQL database sits on. The Log On As for SQL SERVER and SQL SERVER AGENT is [email protected] I have given this user [email protected] access to the directory that the file that I want to send as an attachment is in. I have triple checked the accuracy of the locations. The user [email protected] now has read and execute permissions on C$ on the relevant VM.

The file LCPP_CP.pdf is 72 KB.

The file LCPP_CP.pdf is not read only The profile is public

I can send an email without an attachment. The send_request user in dbo.sysmail_mailitems is domain\SQLReportingAgent

My questions are

• Is there a stored procedure that I can use to check the SQL Engine logged on user?
• Is it possible that msdb.dbo.sp_send_dbmail is not running under the SQL SERVER account?
• If so is there a stored procedure that I can use to check what user msdb.dbo.sp_send_dbmail is using
• Is it possible that this isn't a permissions issue?

My code is below but it is really just boilerplate; the commented lines are the various permutations that I have tried.

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'[email protected]',

@body= 'Test Email Body', 

@subject = 'Test Email 18:01',

@profile_name = 'Team Meeting Profile'
--@file_attachments ='\\VMName\c$\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'\\VMName\c$\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'\\C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments =N'C:\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\VMName\TEAMLists\TEAM\LCPP_CP.pdf'
--@file_attachments ='\\VMName\ListTEAM\LCPP_CP.pdf'

Answer 1

You can check the database mail log by going to management -> right-click database mail -> select view database mail log. Any errors should be in here, for example:

Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-05-16T02:21:47). Exception Message: Cannot send mails to mail server. (A recipient must be specified.). )

Also, you could fire up SQL Server Profiler to check the user for sp_send_dbmail. I think it uses the current user account to send the mail, so I would also confirm your windows account has permissions to the file.

Answer 2

You already mentioned it:

"Is it possible that msdb.dbo.sp_send_dbmail is not running under the SQL SERVER account?"

Thats dosent work anymore since SQL2005 or 2008.

http://msdn.microsoft.com/en-us/en-en/library/ms190307.aspx --> "Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments."

I'm still looking for an solution and found that thread. So there is an answer why it dosent work :)