Lets see how to export SQL Server data into Excel.
First we have to enable the Ad Hoc Distributed Queries option.
Enable Ad Hoc Distributed Queries
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
After enabling it, we can proceed further to write the query for exporting the SQL Serer Data.
Export into Excel Query:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Email FROM [Sheet1$]')
SELECT Name, Email FROM tblnames
GO
Before executing the query, we need to do few steps:
- Provide a Excel sheet path and Create a excel sheet as mentioned in the path. For eg., i have mentioned "C:\testing.xls" created testing.xls in the C:\
- Fill the header row with the columns we provided in the query. For eg., i have Name and Email as columns. I have entered the column names in the excel as show below
Excel File -
testing.xls
- Enter the provider in the query according to the Microsoft Excel version you installed in your version. For eg., i provided Microsoft.ACE.OLEDB.12.0 since i have installed Microsoft Office 2007. This provider works for MS Office 2003 and above. For MS Office 2002 and earlier versions, provider will be Microsoft.Jet.OLEDB.4.0. (Please check it).
- If the sheet name of the Excel sheet varies from the default, then you need to change that in the query. For eg., i have used [Sheet1$] since i did not change the sheet name in my excel file.
Once everything is done, you can execute the above query and you will have the SQL Server data exported into your excel file.
Note: If you fail to create the excel file as mentioned in the query or fail to create the column names as mentioned in the query, you may get the error below:
Error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".