Asked By
carlesy
10 points
N/A
Posted on - 02/12/2012
Hello.
Currently I am using SQL Server Database 2008 and I want to create a linked server and make a connection with Microsoft Access database named Nwind.mdb. I have issued a command in the SQL server Management Studio, like this:
execute sp_addlinkedserver 'North','OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0','d:sql_serverNwind.mdb'
The command was completes successfully.
At this time, I wished to make a query on the customer table in access database and issue the following command:
select * from North…customers
I got the following error message after clicking the execute button:
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.
Any idea how to fix this error? Thanks.
Unable to create a linked server
Dear Carlesy
According to the error message "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.", it seems that u r trying to run a 32 bit office on a 64 bit machine. So u need to use a 64 bit OLE DB provider, the one u r using " Microsoft.Jet.OLEDB.4.0" is not suitable and therefore u got this error. To resolve this issue, Download the MSDASQL provider from microsoft for 64-bit and use it. Also, set the AllowInProgress value in the registry to 1 following this scenario: in the "Run" dialog type regedit. Navigate to the newly installed providers under HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<Instance name>Providers. U'll find the name of the provider u use, right click the entry "AllowInProcess" and modify it to be 1.
So u need to firstly use a 64 bit OLEDB and set the value "AllowInProcess" to be 1.
Hope this may help you.
Best of luck
