CODEWITHSUNDEEP
sql-serverwindowssql-server-2008windows-server-2008-r2
aruni
aruni

Reputation: 2752

How to solve msdb dbo sp send dbmail File attachement in SQL windows server

i need to attach remote desktop files via connect msdb.dbo.sp_send_dbmail

I get an error as in SQL server as below

Attachment file \example.com\E$\Attachments\test.txt is invalid.

However if i type this in RUN command it opens the file correctly, if I set a local file path it works fine with no errors.

What can be the root cause of this issue ?

below is my SQL query.

SET @v_recipients = '[email protected]'
                SET  @v_copy_recipients = '[email protected]'
                    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' ;

Upvotes: 3

Views: 3540

Answers (1)

Dimitar Kyuchukov
Dimitar Kyuchukov

Reputation: 310

Attachments are subject of certain limitations with regards to security context (OS vs SQL server) - probably the remarks section of the sp_send_dbmail documentation might give you a clue:

... 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 ...

Upvotes: 2

Related Questions