Click here to Skip to main content
15,908,015 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
i am running following query to import excel data to sql table

declare @file varchar(100)
Declare @Str varchar(1000)

drop table TempUnitUpdn4
set @file = 'test'

Create table TempUnitUpdn4
(
	col1				Varchar(05),
	col2				Varchar(05),
	col3				Varchar(10),
	col4				Varchar(100)
)

Set @Str = 
'Insert into TempUnitUpdn4
Select Company,Department,UserID,[Proposed action]
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;IMEX=1;Database=D:\'+@file+'.xlsx;'', ''SELECT * FROM [Sheet1$]'')'

print(@str)
execute(@str)



following error is occuring

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.


please help me out.
I goolged it, but not found satisfactory solution.
Posted
Comments
Vamshi Krishna Naidu 23-Dec-12 23:58pm    
Are you using 32 Bit office and 64 Bit Sql Server 2008?

If yes, Then it is going to be a problem. I tried replicating the same on a 32 bit sql server and 32 bit office and it worked for me.

Check this link for last post.
http://www.sqlservercentral.com/Forums/Topic1300136-392-2.aspx

1 solution

No solution,

Office (2007) is 32-bit. Running it on a 64-bit OS makes it's drivers useless in many cases. And there was never a fix for the JET or ACE errors to which you refer.

But, there's good code in KA's article worth seeing.

Excel to SQL without JET or OLE (Version 2)[^]
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900