2

I am saving my query results into a table my_table in SQL server 2008 and then want to insert the data from the table into an excel 2007 file. Below is the query I am using-

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\testing.xls;', 
'SELECT Name, Date FROM [Sheet1$]') 
SELECT a, b FROM my_table
GO

It is showing following error

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

How can I get this fixed? P.S. Ad Hoc Distributed Queries seem to be enabled on my system. enter image description here

Karver01
  • 79
  • 2
  • 10
  • 2
    Make sure you've enabled [Ad Hoc Distributed Queries](http://stackoverflow.com/questions/14544221/how-to-enable-ad-hoc-distributed-queries). Beware that that is a server-wide setting. If your SQL Server is 64 bit, also take a look at [this](http://dba.stackexchange.com/questions/68050/distributed-queries-are-configured-to-run-in-single-threaded-apartment-mode) question. [Here](http://dba.stackexchange.com/questions/54675/openrowset-how-to-configure-ole-db-provider-to-be-used-for-for-distributed-que) is a question where someone is using the newer provider. – Bacon Bits Feb 29 '16 at 18:20
  • Is there a benefit to this over opening `testing.xls` and querying the table? Pull instead of push? – Dick Kusleika Feb 29 '16 at 18:55
  • @BaconBits Ad Hoc Distributed Queries option seems to be enabled on my system. I have updated the question with a snapshot showing the configuration. – Karver01 Feb 29 '16 at 19:11
  • @Dick Kusleika I am not sure if this is the best way to do the job. If you have suggestions please comment! – Karver01 Feb 29 '16 at 19:13

1 Answers1

0

You need to run this before your query:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1
GO

After your query set this values to 0, if you need. You need to use Microsoft.ACE.OLEDB.12.0 if running 64-bit server.

gofr1
  • 15,741
  • 11
  • 42
  • 52