Generate an Excel XLS spreadsheet from T-Sql in Sql Server

Sometimes you find these really old files floating around on your
harddrive and you forget that you ever downloaded them. Here is one
such example. I have no idea where I got this or who to credit for its
creation, but I’ve had it for awhile and came across it and thought it
would be something nice to share with you, as I’m sure it is something
of great help to many of you, especially if you are limited in your
experience on creating DTS packages, which is another way, and
preferred way under most circumstances, to get data from Sql to
Excel. This
is a T-SQL script that uses the system stored procedures sp_OA* for
creating and handling OLE objects, ADO, Jet and a linked server to
create and
populate an XLS file from a select statement. By default, if the
XLS file already exists, the result of the query will get appended to
the worksheet. You’ll have to add some code to check for and
delete the file before creating if that is your desired behavior.
Oh, and I used this a long time ago
with some minor code changes and it worked fine, but this is the
original script using the pubs database, so there are changes you’ll
have to make, and they should be fairly obvious to you.

Note: DTS packages are the
preferred way of handling this type of data transfer, especially when
scheduled, so don’t be hasty to implement this without looking at a DTS
solution first. That being said, I’m sure there are those of you
out there who can find usefulness out of this script.

Create and Excel spreadsheet via T-Sql

– Create XLS script DAL – 04/24/2003

–

– Designed for Agent scheduling, turn on “Append output for step history”

–

– Search for %%% to find adjustable constants and other options

–

– Uses OLE for ADO and OLE DB to create the XLS file if it does not exist

–Linked server requires the XLS to exist before creation

– Uses OLE ADO to Create the XLS Worksheet for use as a table by T-SQL

0×80004005 Microsoft JET Database Engine ‘C:\TEMP\Test_20060423.xls’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.