Using query export SQL Server 2008 table to Excel Sheet

Question

How to export sql server table data to excel sheet.

 insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=D:\Book1.xlsx;', 
'SELECT * FROM [SheetName$]') select TOP 5 CustomerID from Customers

I used the above query but it shows following error

Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

The solution is in blog.sqlauthority.com/2010/11/03/…

VIJAY
– VIJAY

2013-08-29 10:11:05 +00:00
Commented Aug 29, 2013 at 10:11 — VIJAY
– VIJAY, Commented Aug 29, 2013 at 10:11
I Tried that too. Even though it shows the same error.

Golda
– Golda

2013-08-31 05:51:49 +00:00
Commented Aug 31, 2013 at 5:51 — Golda
– Golda, Commented Aug 31, 2013 at 5:51

Golda · Accepted Answer · 2014-02-15 13:15:53Z

1

I have found the solution

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO     
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
 insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=D:\Book1.xls;', 
'SELECT * FROM [Sheet1$]') select TOP 5 CustomerID from Customers

Its working fine

answered Feb 15, 2014 at 13:15

Golda

3,89110 gold badges39 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using query export SQL Server 2008 table to Excel Sheet

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related