Posted: 4/14/2011 6:17:09 AM
I am getting this error while running a cursor on a 64 bit SQL Server R2 on Windows Server 2008 64 Bit Server ." MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode" This Server is sitting on a VM . Basically I am trying to get a csv to be imported into the db and then publish it through SSAS Cube or Power Pivot . The csv data is in a non-uniform manner so I am unable to get it imported through import/ export, Bulk import or thru SSIS package. So I am using the cursor to import data, then use ssis package for transformations and power pivot for publishing . The cursor works fine on a 32 bit machine but throws this error on a 64 bit machine . Following are the workarounds I have tried but these have been in vain.
1) Enable ad hoc querries
2) Execute using SQL job and explicitly running it in 32 bit mode bu checking the check box
3) Running SSIS package in 32 bit mode
4) Downloading and installing the latest access ole db driver from microsoft site .
Even when I try to use openrowset it is throwing same error. At persent I am using the ftp connection to get files in an existing SQL 2000 sql db and then moving those table to SQL 2008 server and publish it from there which is more of a lengthy process but works fine .
Any workaround to get this working on a 64 bit SQL server .......
Posted: 4/14/2011 8:04:06 AM
I remember you personally asked me this issue and i suggested you a few of the workarounds which you have already tried out.
There are a lot of links on web which talks about Workarounds for this problem but no real solution.
The problem is a peculiar one because 64-bit drives for JET are not available. While browsing some of the links i got through this one.
Check out this link and try downloading the latest drivers .but for this you will no longer use Jet rather you will use ACE.
and also check out the important point mentioned there.
I will try to look out for some more info on this in the meantime.
Hope it might help you .
Posted: 4/15/2011 2:59:17 PM
Thanks Sachin, Great to see you answering questions !
Posted: 4/15/2011 3:46:38 PM
Thanks a lot Sachin U always turn out to help ,,,,appreciate of helping me in any situation. We altogether can make this forum a good learning experience.