none
How to attach network file when send email with msdb..sp_send_dbmail, thanks

    Question

  • I need attach file on other server on the same network.

    My sql is on a server named sql8, so I send email like this

        execute msdb..sp_send_dbmail @recipients='wyx1999@hotmail.com',@subject='subject',@body='body',
           @file_attachments='c:\header.gif'

    it works fine, but if I send

    execute msdb..sp_send_dbmail @recipients='wyx1999@hotmail.com',@subject='subject',@body='body',
           @file_attachments='\\sql8\header.gif'

    it gives me error

    Msg 22051, Level 16, State 1, Line 0
    Attachment file \\sql8\header.gif is invalid.

     

    Any idea?

    Sunday, February 25, 2007 6:18 PM

Answers

  • nice ! I tried it again, it works for me, network path works, the mapping doesn't work.

    Do you know if there is any way to give each attachment a new file name, for example, I want to attach two images

    header.gif and footer.gif

    I want to the attach file looks like 1.gif and 2.gif to user.

     

    thanks

     

    Monday, February 26, 2007 6:39 PM

All replies

  • Did you try mapping a drive?
    Sunday, February 25, 2007 6:33 PM
  • yes, I tried, got the same error.
    Monday, February 26, 2007 6:46 AM
  • BOL states:

    A Database Mail account is only used for Database Mail. A Database Mail account does not correspond to a SQL Server account or a Microsoft Windows account. Database Mail supports three methods of authentication to communicate with an SMTP server:

    • Windows Authentication: Database Mail uses the credentials of the SQL Server Database Engine Windows service account for authentication on the SMTP server.

    • Basic Authentication: Database Mail uses the username and password specified to authenticate on the SMTP server.

    • Anonymous Authentication: The SMTP server does not require any authentication. Database Mail will not use any credentials to authenticate on the SMTP server.

    So you need to check while creating a Database mail account, have you specified the authentication method that is required for each SMTP server. Also check the privileges on the speicifed share for the account used that is stored in MSDB to use the DBMail.

    Monday, February 26, 2007 11:00 AM
  • I just tested this at work when I have a variety of servers and it works OK.

    Either your account doesn't have permission or the path is invalid.

    Monday, February 26, 2007 3:59 PM
  • nice ! I tried it again, it works for me, network path works, the mapping doesn't work.

    Do you know if there is any way to give each attachment a new file name, for example, I want to attach two images

    header.gif and footer.gif

    I want to the attach file looks like 1.gif and 2.gif to user.

     

    thanks

     

    Monday, February 26, 2007 6:39 PM
  • Not within Databse Mail. In a stored procedure you could always use xp_cmdshell to copy or rename a file and then call msdb..sp_send_dbmail and attach the copied or renamed file.
    Monday, February 26, 2007 7:11 PM
  • DECLARE @Message VARCHAR(2000), @Subject VARCHAR(500), @FileName VARCHAR(500), @File_exists INT

    SET @Message = 'These are the files from the label run completed at '+ Convert(varchar(20),GETDATE(), 106)+'. Please do not reply to this email, it is automated. If you experience any problem or receive this email when you should not please reply to abcd@hotmail.com. Thank you.'

    SET @Subject = 'Label run files run at: '+ + Convert(varchar(20),GETDATE(), 106)

    SET @FileName = '\\termsvr\d$\users\Subscription\ZZZZ\LabelsContraFed.xls'--;\\termsvr\d$\users\Subscription\ZZZZ\Contrafed\Agents.xls'

    exec msdb..sp_send_dbmail

    @recipients='abcd@gmail.com',

    @subject = @subject,

    @body = @message,

    @File_Attachments = @FileName

     

    It gives me an error

    Msg 22051, Level 16, State 1, Line 0

    Attachment file \\termsvr\d$\users\Subscription\ZZZZ\LabelsContraFed.xls is invalid.

     

    I checked the file in the folder. It exists.

    What is the solution???

    Tuesday, May 15, 2007 1:24 AM