SQL Server Performance Forum – Threads Archive
Send mail with attachment
I’m trying to send a email with attached file using the sp_send_dbmail store procedure (SQL Server 2005). The file is stored into a fieldof a data base table. I succeed to send the mail with the attached file, the recipient correctly receives it (and the attached file) but
doesn’t succeed in opening the file: for example if the file is a .doc the Word application doesn’t read it. In order to retrieve the content of the attached file I set the @query parameter of the sp_send_dbmail store procedure
with ‘SELECT Data FROM AttachmentData WHERE AttachmentId = 78’. The type of the field ‘Data’ is varbinary(max).
The values of the other parameters are: @profile_name = @MyProfileName,
@recipients = @To,
@copy_recipients = @Cc,
@subject = @Subj,
@body = @Content,
@query = @selectAtt,
@query_result_header = 0,
@attach_query_result_as_file = 1,
@query_attachment_filename = @MyFileName,
@exclude_query_output = 1,
@query_no_truncate = 1, If the file to attach is a txt file and the @query parameter is set with
‘SELECT CAST(Data AS varchar(MAX)) FROM TEST_ATTACH.dbo.AttachmentData WHERE AttachmentId = 56’
the mail recipient will be able to open the file.
For the other file tipe (.gif, .doc, .pdf ecc) I try to modify the @query parameter in order to
cast to the varbinary type but it doesn’t work: the recipient is not able to open the attached file. Can help me? Have a Nice day!
Thanks Much.
Any error while opening the attached file? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
when I try to open a pdf file with Adobe Reader an error message is displayed to advise me that the file is corrupted . About the doc file, Microsoft Word display a dialog in order to allow me to choose the correct coding but any language code I choose it doesn’t work. I would like to advise that when I open an attached txt file a new row (‘1 rows effectec’) has been insert at the end of the file. I suppose that this new row has been inserted by SQL Server when process the query to retrieve the data of the file to attach.
Probably the same row is inserted at the end of every file.
How can I avoid this new row insert? Thanks Much.
]]>