Export Data From SQL Server 2005 To Microsoft Excel Datasheet

Hi Friends,

In our day to day working we write queries get there output and then struggle to copy it in EXCEL sheet. Here by I am providing a small query which uses OPENROWSET funtion to fetch data from SQL Server 2005 table to a Microsoft Excel Sheet.

/* First -> Enable Ad Hoc Distributed Queries */

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
/* Second -> Create an Excel Sheet */

1. Create Excel Spreadsheet in root directory c:\Contact.xls (Make sure you name it Contact.xls).
2. Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName.
3. Run following code in SQL Server Management Studio – Query Editor.

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')SELECT TOP 5 FirstName, LastNameFROM Person.Contact
GO
Open Blocking.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.
Make sure your spreadsheet is closed during this operation. If you want to change name of your Spreadsheet name or Sheet1 name to desired name you can do that but ofcourse you have to mention those names in above .
Regards
GURSETHI