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.