Tuesday, March 10, 2009

Linked server for 64-bit SQL 2005 SP3

I was supposed to set up the linked server from SQL Server to Oracle 10g database. Initially, I tried to use the Microsoft provider. After registering the 32-bit MDAC for Oracle and setting up the linked server, I got an error:
  • C:\Program Files (x86)\Common Files\System\Ole DB>regsvr32 msdaora.dll
  • EXEC sp_addlinkedserver 'CFS', 'Oracle', 'MSDAORA', 'oracle10g'
  • EXEC sp_addlinkedsrvlogin 'CFS', 'FALSE',NULL, 'scott', 'tiger'
  • SELECT * FROM CFS..SCOTT.SOME_VIEW
  • 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.
This was the 32-bit provider and apparently this is a known bug.

I switched to Oracle OLEDB Provider and it worked without issues:
  • USE [master]
  • GO
  • EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
  • GO
  • EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1
  • GO
  • EXEC master.dbo.sp_addlinkedserver @server = N'CFS', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'CFS'
  • EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CFS',@useself=N'False',@locallogin=NULL,@rmtuser=N'scott',@rmtpassword='tiger'
The other solution would be to install 64-bit MDAC on the Windows server. MDAC has been complied to both 32-bit and 64-bit files since the version 2.7, but it is limited to only few platforms and we couldn't find 64-bit MDAC for Windows 2003.

The only drawback with using the Oracle provider is that for any errors, we have to go to Oracle to get a support.
Posted by at

2 comments:

Unknown said...

Thanks for posting this. Since moving to 64-bit SQL Server 2008 on Win2008r2, I've had nothing but problems setting up a stable linked Oracle server. Your post provided an option that I hadn't tried & while I did not have success with it, I did appreciate the time that you put in to write it up. Here's the error that I'm receiving with the linked server config that you summed up:

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXX". (Microsoft SQL Server, Error: 7302)

March 12, 2010 at 7:52 AM
Rx said...

I know it is an old site.
When running the script after installing the ORAOLEDB.ORACLE provider, the servers will not show up.
My ODBC has the same issue, the list of servers don't drop down. However, when I put the server name in ODBC, it reports connection successful.
In my Windows 7 (32 bit) the server names list out.

Is there a port or application to add in the firewall to allow sp_helpserver to show available servers?
Regards

October 2, 2013 at 9:28 AM

Post a Comment

Subscribe to: Post Comments (Atom)