Unable to create a linked server

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.

• Status: Open
• Question Views: 1630
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

Answered By tibor.tokie 25 points N/A #96606

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

About tibor.tokie

Questions
1

Answers
157

Best Answers
24

• Vote Up 0 Vote Down

• Posted on - 02/13/2012
• Question Category: MS SQL Server