Post reply

Format error when using SendMail with an Excel attachment

  • February 3, 2004 at 1:39 pm

    #108857

    We are using xp_sendmail to attach the results of a stored procedure to an email.  The process works perfectly in that the results are put in an attached file and then emailed to the user.  The problem is that the file must be compatible with Microsoft Excel.  The file is formatted as a CSV file, however, Excel does not recognize the format.  By using a Hex editor, I found that there is an empty hex value between each character in the file.  In other words if I have the word Database in the results, then there is an empty hex value between the letter D and A and every other letter.  If I simply copy and paste the data from the sqlmail created file to another file the empty hex characters are no longer there and the file imports fine.

    I assume the issue is that the data is stored as unicode and therefore uses 2 bytes.  I have tried (unsuccessfully) to use the COLLATE property with numeous other collations but the results still remain the same.

    Any suggestions?

  • February 6, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • February 6, 2004 at 8:46 am

    #493095

    I Firmly believe that xp_sendmail IS NOT the tool for that job!

    I've  had a lot of problems of this kind and the workarounds didn't work pretty well. My advice is to use DTS and you will save time and effort !!

     

    HTH

     


    * Noel

  • February 12, 2004 at 8:52 am

    #493820

    You can try also the xp_smtp_sendmail

  • February 23, 2004 at 3:37 pm

    #495225

    Apologies for the very late reply ... monitoring this forum is not at the top of the priorities for my job.  There is a switch on the xp_sendmail command that is not documented in BOL.  It may fix your problem.

     @ansi_attachment=True,

    If it doesn't, you may have to use the "convert" function to change some of your fields from nvarchar to varchar.

    Using CSV format does have severe limitations as there seems to be no way to make Excel read/display the data in the format that you may want it to.  This particularly applies to character fields that only contain digits!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply