Caveman's Blog

My commitment to learning.

SQL Server: Export data to Excel

with 5 comments


This article will demonstrate one of the several ways of exporting data from SQL Server 2005 to an Excel spread sheet. This time we will use simple SQL queries to export data unlike the earlier instance when we used SSIS. The precursor to achieving this will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server. This can be accomplished by using the SQL Server 2005 Surface Area Configuration Utility or by executing the following code in SQL Server Management Studio’s Query Editor:

sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
reconfigure

When the server is capable of running Ad Hoc Distributed Queries we are all set to run SQL queries to export data to Excel spreadsheet. It must kept in mind that an Excel template with all the intended columns has to exit before data can be exported and the file must be closed while data is transferred. In the following example data from the “employee” table in the SQL Server 2005 database is being exported to an Excel spreadsheet named “EMP”.

--Export data to Excel
insert into openrowset('Microsoft.Jet.OLEDB.4.0' ,
'Excel 8.0;Database=c:\test\test.xls','Select * from [EMP$]')
select empid, ename from employee

References:
1. Server Settings Configuration Options

Advertisements

5 Responses

Subscribe to comments with RSS.

  1. […] SQL Server: Export data to Excel […]

  2. asp.net, c#,javascript…

    […]SQL Server: Export data to Excel « Caveman's Blog[…]…

    asp.net, c#,javascript

    September 29, 2011 at 6:03 am

  3. Web Design Guide…

    […]SQL Server: Export data to Excel « Caveman's Blog[…]…

    Web Design Explained

    October 28, 2011 at 8:57 am

  4. rueben…

    […]SQL Server: Export data to Excel « Caveman's Blog[…]…

    rueben

    November 19, 2011 at 7:56 pm

  5. NDT Blog…

    […]SQL Server: Export data to Excel « Caveman's Blog[…]…

    NDT Blog

    December 22, 2011 at 6:03 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: