2

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 SQLReportingAdmin@lan.domain.com I have given this user SQLReportingAdmin@lan.domain.com 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 SQLReportingAdmin@lan.domain.com 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'valid@emailaddress.com',

@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'
  • Can you check if your profile is Public or not ? Also check if the file is not locked or readonly (in File properties) ? – Kin Shah May 15 '14 at 20:32
  • Thanks Kin,The file LCPP_CP.pdf is not read only and the profile is public. – Rascal of the North May 15 '14 at 20:44
  • Have you checked your event viewer on the file server VM to see if there is some kind of login error message? Also, can you check SQL Server logs to see if there is some kind of login error message there? – efesar May 15 '14 at 20:57
3

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.

  • I used profiler and in the TextData field the entry was exec msdb.dbo.sysmail_help_admin_account_sp the Application name was DatabaseMail and the login name was domain\VendorCluster (a name I wouldn't have guessed in a billion years). I gave domain\VendorCluster rights to the shared drive and I no longer get the error above but a mail queued message. The mail hasn't arrived yet but this has answered my question. – Rascal of the North May 16 '14 at 14:02
0

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.