Geeks With Blogs
Nagendra Prasad
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                           

A B C    
1 Name Email  
2      

  • 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)".






Posted on Thursday, March 26, 2009 11:32 AM | Back to top


Comments on this post: Export SQL Server Data into Excel

# re: Export SQL Server Data into Excel
Requesting Gravatar...
Thanks now i understand
Left by Peter on Sep 10, 2009 4:22 AM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
very much helpful content...
Left by mohan on Feb 10, 2010 6:25 AM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
Query:
USE [DB] ;
GO
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=C:\1.xls;',
'SELECT Fcityname FROM [1$]' )

SELECT Fcityname FROM TCITYS
GO

I get error message:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

File 1.xls exists and have all field names (such as TCITYS table).
1.xls closed when I make query


Can you say what I do wrong?
Thanks


Left by Yuri on Feb 17, 2010 8:58 AM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
Hi Yuri,

Your Excel sheet name should be "1" as per your query mentioned in your comment(SELECT Fcityname FROM [1$]). And make sure column "Fcityname" exists in the created excel sheet (1.xls).
Please let me know, if that doesn't work for you.
Left by Nag on Feb 24, 2010 7:25 PM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
thank you for that post. it is very useful.
Left by Metaphysical Sciences on Mar 16, 2010 8:40 PM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
Pls explain what is [sheet1$] ?
Left by Rahoul Ghosh on Nov 19, 2010 7:34 AM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
hi,

I am using this query to export data to excel 2007.

When I am running a below query I have getting a error message which I have pasted below.

My excel sheet location is as follows "d:\contact.xlsx" & there are two column name which are similar to query column name "FirstName", "LastName".

I also wanted to know that file location "d:\contact.xlsx" is to be on client machine or it must be located on main server (I am on client machine).
Please give me solution regarding this.


Query
-------

INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=d:\contact.xlsx;',
'SELECT * FROM [Sheet1$]')
SELECT LoginName as FirstName, UserName as LastName
FROM sysuser
GO


Error Message
---------------

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)".

Left by Abhinav on Feb 02, 2011 7:54 AM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
Heres Cialis juridiska nyheter blixt för potenspiller Little Miss med henne inför målade med globspotenshöjandemake upp: Illegala utlänningar har ingen konstitutionella rättigheter. Du är inte Cialis amerikansk medborgare. Smuggling själv över potenspiller gränsen, regardlesspotenshöjandecountrypotenshöjandeorigin, är i violationpotenshöjandeAmerican invandringslagar. potenspiller definitionpotenshöjandeillegal isnt rasistiska eller rasism. Om du bryter in i någons housepotensmedelsteal, som är Cialis olaglig handling, inte rasism. Om potenspiller lag säger Cialis man Köp Viagra kan tvinga Cialis womanpotensmedelhave kön potenspiller hennes vilja, det kallas våldtäkt och om du begår en sådan handling Cialis, Cialis sin olagliga en, inte rasism.
Left by sumkin on Mar 01, 2011 11:46 AM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
Could not locate entry in sysdatabases for database 'AdventureWorks'. No entry found with that name. Make sure that the name is entered correctly.
Left by Kiran Kumar on Jan 19, 2012 6:44 PM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
Very helpful. NIce Solution
Left by Acer on May 23, 2012 9:46 PM

# re: Export SQL Server Data into Excel
Requesting Gravatar...
When i user this query i get below error
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7390, Level 16, State 2, Line 2
The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

i'm using msexcel2010 and after exec. this query my excel file was currept..

Left by ashish radadia on Jun 06, 2014 7:26 PM

Your comment:
 (will show your gravatar)
 


Copyright © nagendraprasad | Powered by: GeeksWithBlogs.net | Join free