if you are trying to setup a linked server between SQL and Oracle using
the familiar MSDAORA in a 64bit environment your out of luck.
microsoft has confirmed that they DO NOT support the MSDAORA provider
in in 64bit environments. you’ll notice this straight-away when looking over
the providers list from management studio under Linked Servers. any Oracle
provider or variant is missing from the list. remember that this has nothing
to do with how you installed and configured your SQL Server. it simply is
no longer offered.
you have to use the Oracle driver ( ORAOLEDB.oracle ) which you get automatically
when you install the Oracle client. once installed you’ll see the ORAOLEDB.oracle
provider listed in management studio.
suppose you have a 64bit SQL Server, and you are trying to connect to a 32bit
Oracle instance… that just makes things more difficult.
the conventional wisdom is that you need to have 64bit environments for both data
sources to get it to work.
i’ve read though; that the setup is possible if you have both the 64bit Oracle client
installed along side the 32bit Oracle Client.
this information may not be entirely new for some; but thought i would mention
it regardless. might save you call into ms over creating a linked server.