We have a linked server that is an Apartment model OLE DB Provider. It works fine on SQL 2005 SP1 and previous versions.
After applying SP2, we get the following error message when running a query against the linked server.
I cannot find anything in the SP2 documentation that indicates a change of behavior for linked servers.
Any ideas ?
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'XXX.XXXXX' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
TobyHi, I have the same problem with MSDAORA when I try to execute a query using a Linked Server with Oracle Server."Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"
Someone can help us?
Thanks
Denis
|||
I'm not in a position to answer about the change in the linked server code, but perhaps it is possible to reconfigure the appartment model.
What do you have under HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32\ThreadingModel?
Can you try setting it to "Both" and see if you can connect?|||Ditto,
Did you find a solution?
Thanks.
|||Did you try changing apartment model? Does it not work for you?|||No, Basically I found out that the 32 bit OLEDB Provider will not work correctly on 64Bit Server 2003 with SQL 2005 SP2 64Bits. I ended up installed the Oracle 64Bit Provider and use OraOLEDB.Oracle provider.
Here's the complete thread:
I'm trying to get SQLServer 2005 to link to an Oracle 10g Database.
The OS is Server 2003 SP2 64Bit.
The Microsoft OLE DB For Oracle driver is installed (MSDAORA) but doesn't appear in SQL Server Providers.
It's a 32 Bit provider.
Since it didn't appear, I used T-SQL sp_addlinkedserver (http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
http://www.sqlmag.com/Article/ArticleID/49687/sql_server_49687.html) to add my linked server.
When I tested the link, it told me MSDAORA didn't exist. So I ran regsvr32 on "C:\Program Files (x86)\Common Files\System\Ole DB\msdaora.dll" to re-register the Provider.
After that, when executing some T-SQL against Oracle, I got a new error:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
But the registry shows:
[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA"
"OLEDB_SERVICES"=dword:ffffffff[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors]
@.="Extended Error Service"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ExtendedErrors\{e8cc4cbf-fdff-11d0-b865-00a0c9081c1d}]
@.="MSDAORA ErrorLookup"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\InprocServer32]
@.="C:\\Program Files (x86)\\Common Files\\System\\Ole DB\\msdaora.dll"
"ThreadingModel"="Both"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\OLE DB Provider]
@.="Microsoft OLE DB Provider for Oracle"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\ProgID]
@.="MSDAORA.1"[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}\VersionIndependentProgID]
@.="MSDAORA"
>>>
Looks like SQL 2005 SP2 may have caused the problem.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1433118&SiteID=1
>>>
Use Oracle 64Bit drivers : http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
>>>
But after creating the Linked Server I got:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "IBM_EDBD".
>>>
For 64-bit Distributors, Oracle publishing uses the Oracle OLEDB Provider for Oracle (OraOLEDB.Oracle). SQL Server creates a registry entry to allow the Oracle provider to run in process with SQL Server. If there is a problem reading or writing this registry entry, the following error message is shown:
"Unable to update the registry of distributor '%s' to allow Oracle OLEDB provider OraOLEDB.Oracle to run in process with SQL Server. Make certain that current login is authorized to modify SQL Server owned registry keys."
Oracle publishing requires the registry entry to exist and to be set to 1 for 64 bit Distributors. If the entry does not exist, SQL Server will attempt to create it. If the entry exists, but is set to 0, the setting will not be changed; the configuration of the Oracle Publisher will fail.
To view and modify the registry setting:
1. Click Start, and then click Run.
2. In the Run dialog box, type regedit, and then click OK.
3. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Providers.
Included under Providers should be a folder named OraOLEDB.Oracle. Within this folder should be the DWORD value name AllowInProcess, with a value of 1.4. If you determine that AllowInProcess is set to 0, update the registry entry to 1:
a. Right-click the entry, and then click Modify.
b. In the Edit String dialog box, type 1 in the Value data field.
So in conclusion:
To create a linked server on SQL Server 2005 SP2 64Bits,
- Install 64Bit Oracle Client
- Install 64Bit Oracle OLEDB Provider
- Set the AllowInProcess value in the registry
No comments:
Post a Comment