Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_send_dbmail attachement EXCEL file has error Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 12:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:34 AM
Points: 8, Visits: 138
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.
Post #1370180
Posted Wednesday, October 10, 2012 5:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 7,098, Visits: 12,605
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
Post #1370845
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse