Post reply

sp_send_dbmail attachement EXCEL file has error

  • October 9, 2012 at 12:47 am

    #99728

    Hi All,

    Need you help on below.

    I am generating the excel file and send email using "sp_send_dbmail". I even receive the email , but when i open the excel attachement has error shown as below.

    ERROR:

    The file you are trying to open,'~4595071.xls', is in a different format that specified by the file extension. Verify that the file is not corrupted and is from a truseted source before opening the file.

    SOURCE CODE:

    DECLARE @CURRDATE VARCHAR(10)

    DECLARE @tab_char CHAR, @subject nvarchar(150), @query_attachment_filename nvarchar(150), @recipients nvarchar(300)

    SET @CURRDATE = CONVERT(VARCHAR, GETDATE(),112)

    SELECT @tab_char ='',

    @subject = 'Extraction file - ' + @currdate,

    @query_attachment_filename = 'Extract_' + @currdate + '.xls',

    @recipients = 'abcd@xxxx.com'

    -- schedule extract to send alert

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = @recipients,

    @body = 'This document is to send extract',

    @subject = @subject,

    @profile_name = null,

    @query ='SET NOCOUNT ON;

    SELECT '''',''HEADER1'',''HEADER2'',''HEADER3'',''HEADER4'',''HEADER5'',''HEADER6'',''HEADER7'',''HEADER8''

    UNION ALL

    SELECT SELECT * from FROM ##EXTRACT',

    @attach_query_result_as_file = 1,

    @query_result_separator = @tab_char,

    @query_result_width = 32767,

    @exclude_query_output = 1,

    @query_result_no_padding = 1,

    @append_query_error = 1,

    @query_result_header = 0,

    @query_attachment_filename = @query_attachment_filename

    Thanks,

    Sunil.

  • October 10, 2012 at 5:04 am

    #1547588

    Just because you slap a .xls extension onto a file does not make it an Excel-format file. What you have is a CSV (Comma-Separated Value) file saved with a .xls extension. Yes, when someone double-clicks your email attachment it will open in Excel if they have it installed because that is the program Windows is configured to use to open files with a .xls extension when Excel is initially installed but the Excel program can tell that the file is not in Excel-format and that is the warning you are seeing. You would be better off saving the file with a .csv extension. If the email recipient has Excel installed then the file will still open in Excel because just like files with an .xls extension Windows is also configured to open .csv files in Excel when Excel is installed.

    edit: spelling

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

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