sql-server - msdb dbo sp send dbmail File attachment in SQL windows server 如何解决

Question

我需要通过连接 msdb.dbo.sp_send_dbmail 附加远程桌面文件

我在 SQL Server 中收到如下错误

附件文件\example.com\E$\Attachments\test.txt 无效。

但是，如果我在 RUN 命令中键入它，它会正确打开文件，如果我设置本地文件路径，它会正常工作，没有错误。

这个问题的根本原因是什么？

下面是我的 SQL 查询。

SET @v_recipients = 'email@example.com'
                SET  @v_copy_recipients = 'email2@example3.com'
                    SET @v_body =  'test mail'
                    SET @v_subject =  'Test mail'
                    SET @v_email_template_id = 1
                SET  @v_queue_id = 1
                    SET @v_attachments = N'\\example.com\E$\Attachments\test.txt'           

set @p_database_profile_name='profile_name'

EXEC msdb.dbo.sp_send_dbmail
                @recipients = @v_recipients,
                @copy_recipients = @v_copy_recipients,
                @body = @v_body, 
                @subject = @v_subject,
                @file_attachments= @v_attachments,

                @profile_name = @p_database_profile_name,
                @body_format = 'HTML' ;

Answer 1

附件在安全上下文(操作系统与 SQL 服务器)方面受到某些限制 - 可能是 sp_send_dbmail documentation 的备注部分 可能会给你一个线索:

... Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on ...