August 6, 2002 at 12:25 pm
I've tackled this two different ways, but haven't found the exact answer you want. Here's what we've done.
To write to an Excel file, the best tool available IMHO is Excel Writer from http://www.softartisans.com/. It's a great utility to turn recordsets into Excel spreadsheets. It'll allow you to do any formatting you need, it's designed to live on the server, and it's FAST. Probably overkill for what you're trying to do though.
That said, there's also an extremely low tech answer. If all you need is to get your data into a spreadsheet with the field names in the first column, try this.
declare @sql nvarchar(4000)
set @sql = 'bcp '
set @sql = ' " select field1, field2 from OutPutTable '
set @sql = @sql + ' union '
set @sql = @sql + 'select ''field name 1'', ''field name 2'' " '
set @sql = @sql + ' queryout "\\myserver\myshare\myfilename.xls" -T -c'
exec master..xp_cmdshell @sql
The query will return your field names in the first row, and the file created by the bcp command will be in tab delimited format. With the .xls extension, if a user double clicks on it, it will open in Excel, and Excel will load each field into it's own column. One caveat. You must cast or convert numeric and date data to varchar or the union operation will fail.
Like I said, it's not exactly what you're looking for, but both of these methods work.
Good luck!
John
August 7, 2002 at 1:25 am
Thanks, I'll give them a try
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply