SQL Server Provider?

Question

How to solve this 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.

Answer 1

I just did a bit of research on this myself. Here's the MSDN article that describes the error and provides a basic resolution:

http://msdn.microsoft.com/en-us/library/cc645919.aspx

Information on what single threaded and multi threaded apartments are:

http://stackoverflow.com/questions/485086/single-threaded-apartments-vs-multi-threaded-apartments

This was a page where someone had the problem and a number of possible solutions were posted:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119887

From the research i've just done, you might be trying to connect to a linked server that may be running Oracle. You'll need to use a different provider to connect to it. That's based on what you've provided, and what i've found.

Answer 2

I'm guessing you're using an Access database and you're trying to run a query that joins between that Access database and a server based system like SQL or Oracle. Thing is that won't work (you've seen the error message). For a database to be able to handle queries across two or more separate systems it needs to support some kind of distributed transaction support and that's not Access.

I think the best solution for this is to move the data from Access into SQL or vice versa.