Export sql query result in excel file

Question

0.00/5 (No votes)

See more:

Hi
I want save my sql query result as excel file.
I use from bellow code, but it doesn't work correctly:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [Sheet1$]') 
select * from DataDictionary

My Error:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I use SQL Server 2012 and Excel 2007.

Please help me, How I can save result of my query as excel file?
Thanks a lot

Posted 7-Jul-13 23:42pm

Reza Alipour Fard

Add a Solution

Comments

Member 10463904 21-Feb-14 2:12am

Pls help me how to export sql query result in excel... Tell me full coding explanation.. im new to sql

Reza Alipour Fard 21-Feb-14 14:13pm

Please see my solution, I used from bellow query. for example:
DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp "Select * From TestTable" queryout "C:\TestFile.xlsx" -U sa -P 123 -c'
Exec xp_cmdshell @cmd

2 solutions

Solution 1

Have a look here:
Query Excel file source through Linked Server[^]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926[^]
http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/[^]

Example:

SQL

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=D:\testing.xlsx;', 
'SELECT * FROM [Sheet1$]') 
select * from DataDictionary

Another way is to use bcp and xp_cmdshell tools (to *.csv files):
Exporting Data Programatically with bcp and xp_cmdshell[^]

Posted 7-Jul-13 23:49pm

Maciej Los

Updated 8-Jul-13 0:49am

v3

Comments

Reza Alipour Fard 8-Jul-13 6:05am

I use from all of the introduced ways, but all of them is not work correctly.

Reza Alipour Fard 8-Jul-13 6:06am

Please attend to my problem after that give me a clear sample.

Maciej Los 8-Jul-13 6:10am

See my answer now ;)

Reza Alipour Fard 8-Jul-13 6:33am

I have erro:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Maciej Los 8-Jul-13 6:45am

Use solution from here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access[^] (first soultion by Ying Lin).

Reza Alipour Fard 8-Jul-13 7:00am

Last problem has been solved.
But I have a new error.
Msg 7438, Level 16, State 1, Line 1
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.
My SQL Server is 64-bit.

Maciej Los 8-Jul-13 7:17am

So, install MS Access 2010 drivers for 64 bit WinOS ;) which works with 2007 ;)
http://answers.microsoft.com/en-us/office/forum/officeversion_other-access/64-bit-driver-for-dsn-for-ms-access-database/be8c0ad4-d8fd-48e0-9026-b95d84135820[^]

Solution 2 by Paul Shkurikhin

Reza Alipour Fard 8-Jul-13 9:52am

I download and install 2007 Office System Driver: Data Connectivity Components and Microsoft Access Database Engine 2010 Redistributable (32Bit). But I have bellow Error:
Msg 7438, Level 16, State 1, Line 2
The 32-bit OLE DB provider "Microsoft.Jet.OLEDB.4.0" cannot be loaded in-process on a 64-bit SQL Server.
My Computer OS: 64 Bit
My SQL:64 Bit
My Office: 32 Bit

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Reza Alipour Fard · Accepted Answer · 2013-07-13T22:46:00

Solution 3

Hi
I solved my problem.

SQL

DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp "My Select Query Here" queryout "My Excel File Path Here" -U sqluserHere -P sqlpasswordHere -c'
Exec xp_cmdshell @cmd

Posted 13-Jul-13 22:46pm

Reza Alipour Fard

Comments

Meysam Toluie 26-Apr-14 4:43am

I executed this code:
DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp "Select Distinct Loan.ManagedFundAccNo1 From Account.Loan.Loan" queryout "D:\test.xls" -U sa -P 123 -c'
Exec xp_cmdshell @cmd

Then the following errors came as result.

output
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired
NULL

Reza Alipour Fard 2-Jun-14 10:17am

Please check Xp_Cmdshell status in your SQL Server.

Member 11027043 22-Oct-15 23:30pm

Hi, this code works perfectly :)

DECLARE @cmd VARCHAR(255)
SET @cmd = 'bcp "My Select Query Here" queryout "My Excel File Path Here" -U sqluserHere -P sqlpasswordHere -c'
Exec xp_cmdshell @cmd

But I need the column name.
How can I get the column name at the first row in my excel?