SQL Server Performance

Send mail with attachment

Discussion in 'SQL Server 2005 General Developer Questions' started by janao, Oct 25, 2006.

  1. janao New Member

    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 field
    of 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.
  2. satya Moderator

    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.
  3. janao New Member

    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.

Share This Page