Saturday, April 14, 2007

Export data from SQL Server to Excel without using SSIS or DTS

Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005). For some reason if at all you want to do it via query read on:

Step 1: Execute the code snippet

Exec sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
Go
Reconfigure;
Go

Step 2: Create the excel file and then add the headings in the .xls file. [Important] The heading should be the same as that of the table columns.

Insert into Openrowset ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\VadivelTesting.xls;' , 'Select * from [ProductInfo$]') Select ProductID, ProductName from dbo.tblProducts

Points which might interest you:

1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\VadivelTesting.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).

2. Instead of "ProductInfo" replace it with your excel worksheet name.

2 comments:

Deep Trouble said...

Hello Vadevel,

I wanted to know how to reverese the Ad oc settings and the show advanced options that we enable?

Regards
Dee

NAT said...

This is very cool, but the only problem - it imports all numbers as text into Excel. Therefore if you want later to do any math with data - you can't!! Formatting template columns into Numbers - doesnt work either.