November 7, 2008 at 8:06 am
Hi all,
I'm trying to send a sql statement using sp_send_dbmail procedure. Result should be attached as an excel file. Unfortunately the format of decimal numbers do not fit to German format. So German Excel does not recognize this as a figure but rather as text 🙁
Does anyone has an idea how to handle this?
declare @sql varchar(1000)
set @sql='select top 10 SubTotal from AdventureWorks.Sales.SalesOrderHeader'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'my_profile',
@recipients = 'my_recipient',
@body='testbody',
@subject = 'testsubject',
@query = @sql,
@attach_query_result_as_file=1,
@query_result_separator='',
@query_result_header=1,
@query_attachment_filename='result.xls'
Result should be like:
SubTotal
24643,9362
1553,1035
But in Excel I find:
SubTotal
246.439.362
15.531.035
Thanks in advance
smerg
November 7, 2008 at 8:51 am
Its not an Excel file, so you can't do excel specific formatting. You are essentially creating a csv file and calling it .xls which Excel automatically opens and applys local formatting to.
So you I would suggest a couple options.
1. Look at formattig the data in the select so that when it comes in its formatted properly.
2. Use SSIS or another mehtod to create an actual real XLS file and not a csv.
3. Do post formatting once in Excel with a macro.
Hope it helps.
November 16, 2008 at 11:56 pm
Hi dmc,
thanks for your answer. I created a SSIS but this causes some overhead. I hoped to get a solution as general and as easy like sp_send_dbmail.
Best Regards
Smerg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply