x

How to add linked server provider 'MSDAORA'

I need to create a linked server to an Oracle database but there is no 'MSDAORA' provider. How do I add the provider?
more ▼

asked Mar 21, 2012 at 04:59 PM in Default

rocky_ca gravatar image

rocky_ca
4 2 2 2

I've installed the Oracle client, both x32 and x64. The OraOLEDB.Oracle provide became available for me to create the linked server but now I'm getting the error:

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TEST". OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" returned message "ORA-03134: Connections to this server version are no longer supported.". (Microsoft SQL Server, Error:7303)

I'm connecting from SQL Server 2008R2 to an Oracle 10g database.

Any ideas?
Mar 27, 2012 at 10:05 PM rocky_ca
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You just need to install Oracle Client on your SQL Server. This way the Provider you need will become available in the providers dropdown when you setup your linked server. I should note that the provider should be OraOleDB.Oracle not MSDAORA. After you install Oracle Client and add your linked server, please do one more step to check the properties of your OraOleDB.Oracle provider: it needs to have the option Allow inprocess enabled, like this:

alt text

Also, you might want to make sure that your connection settings to your Oracle database are setup properly (under Security tab of your linked server property pages):

![alt text][2] Oleg

[2]: http://ask.sqlservercentral.com/storage/temp/152-z_linked_props.png
\z_provider.png (56.9 kB)
more ▼

answered Mar 21, 2012 at 05:47 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
  • List item

+1 to @Oleg. I only add, that there are issues with x64 machines. I mean, you need to do the installation of the Oracle Drivers in appropriate way to be able to access oracle from x64 version of SQL Server as well as from x32 tools like BIDS (SSDT).

This steps are for the 11g client installation (you can access earlier versions of oracle as well using the 11g client).

You need to install first x32 version of Oracle Drivers followed by x64 version of drivers.

During installation you have to ensure, you are installing:

  • Oracle Call Interface
  • Oracle Net Client
  • Oracle Provider for OLE DB

After installation some adjustments in registry are necessary. Under registry Keys

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI 

You have to change Values of entries:

OracleOciLib = oci.dll 
OracleSqlLib = orasql11.dll
OracleXaLib = oraclient11.dll 
After Rebot and following @Oleg answer you should be able to create the Linked Server and use it. You will probably be able to create it even without following @Oleg's answer, but in that case you will be able to query the Linked Server, but you will be unable to browse the linked server views, tables etc. in SSMS.
more ▼

answered Mar 21, 2012 at 07:16 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x110

asked: Mar 21, 2012 at 04:59 PM

Seen: 8370 times

Last Updated: Mar 27, 2012 at 10:05 PM