Exporting data from sql server table to excel file

Exporting data from sql server table to excel file requires two levels to query execution. They are given below.

1. Enabling OPENROWSET (executed by DBA)
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

2. This is second part by which actually data will be exported from sql table to excel file. Here is the query worked for me.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\testing.xls;',
'SELECT BusCatID,Description FROM [Sheet1$]')
SELECT BusCatID,Description FROM hooser_burger_db.dbo.BusinessCategory

Before executing the query developer has to create an .xls file and he/she has to denote the header or column name of sql sever tables to be exported in the excel file. Then after running the query he can consume the data from the file.

Masud (7-2-2010)