CODEWITHSUNDEEP
sql-server-2008t-sql
Ghost Rider
Ghost Rider

Reputation: 207

sp_send_dbmail does not attach the file to the email

I have the following code: 

DECLARE

@ATTACH_FILE nvarchar (max),
@SQL varchar (max);

/* ============================================================== */  

-- add files to attach
    SET @ATTACH_FILE = N'\\C:\attach\file.txt';

SET @SQL = 'exec msdb.dbo.sp_send_dbmail
@profile_name = ''ProfileMail'',
@recipients = ''[email protected]'',
@reply_to = ''[email protected]'',
@subject = ''subject mail'',
@body = ''body mail'',
@file_attachments='' '+ @ATTACH_FILE +' '' ';

-- send mail
    EXEC(@SQL);

UPD remove the two slashes SET @ATTACH_FILE = N'C:\attach\file.txt'; - the error persists

I receive an error: Invalid file attachment \C:\attach\file.txt (file is located on a sql server).
I do not understand what the problem is.

Upvotes: 0

Views: 4147

Answers (3)

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

The error is about '//' and extra space in attachment address. It will work now. 

declare @attach_file nvarchar(100),@sql varchar(max)
   SET @ATTACH_FILE = N'C:\attach\file.txt';

SET @SQL = 'exec msdb.dbo.sp_send_dbmail
@profile_name = ''ProfileMail'',
@recipients = ''[email protected]'',
@reply_to = ''[email protected]'',
@subject = ''subject mail'',
@body = ''body mail'',
@file_attachments='+char(39)+@ATTACH_FILE+char(39);

-- send mail
   Exec(@sql)

Upvotes: 1

MaheshMajeti
MaheshMajeti

Reputation: 187

As you specified that file is in Sql server.Find out the exact address of the file using below query.

SELECT name, physical_name AS current_file_location
   FROM sys.master_files

And use the address in your mail attachment.

Upvotes: 0

Vijay Hulmani
Vijay Hulmani

Reputation: 979

Replace this

SET @ATTACH_FILE = N'\\C:\attach\file.txt';

with this

SET @ATTACH_FILE = N'C:\attach\file.txt';

Upvotes: 0

Related Questions