February 7, 2007 at 8:54 am
Hi,
My sql server is running the following version: Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
I am trying to add some oracle linked servers and I only see the 2 drivers installed:
1. SQL Native Clent 2. SQL server
MDAC version in the registry shows 2.82.8220
I am trying to figure out whether there is something wrong with the installation or I need to install the drivers separately.
Could someone please check the control panel-->Adminitrative tools-->Data Sources(ODBC)-->Add and tell me the list of drivers?
Any help on this will be greatly appreciated.
-Nikki
February 7, 2007 at 11:49 am
Nikki,
I have a lot on the list because we have things to be installed on the server separetely. We did have to install Oracle client (or connectivity tools) for us to be able to create a linked server.
Regards,Yelena Varsha
February 28, 2007 at 9:58 am
Was this ever resolved? We are missing ODBC drivers (including ORACLE) on our 64 bit SQL Server 2005 environments. Server operations group installed the Oracle client, but the drivers are still missing. Maybe they missed something?
February 28, 2007 at 10:22 am
I always used to use MSDAORA to connect to the Oracle servers, but there isn't one for x64.
So you need Oracle 10g client for 64 bit, then a couple of patches from metalink (5203839 and 5752544 for platform MS Windows Server 2003 (AMD64 and EM64T))
Before installing 5752544, you need to download, from
http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
Oracle10g Release 2 ODAC (64-bit) 10.2.0.2 for Windows x64
Download the File
64-bit ODAC 10.2.0.2 Beta for Windows x64
It is apparently the production version even though it says Beta.
When you create your linked servers you need to set collation compatible to TRUE otherwise you'll get this error
CANNOT FETCH ROW USING BOOKMARK FROM OLE DB PROVIDER ORAOLEDB.ORACLE LINKED SERVER
One last thing - since it's the 10g client you will no longer be able to connect to versions of Oracle before 9i
Hope this is useful
IainT
November 13, 2007 at 2:04 pm
I cannot find the two patches listed however the link now points to a 10.2.0.3 rather 10.2.0.2 so maybe the patches are no longer necessary. However, I am having a little trouble actually using the driver to build a linked database.
I have a 64 bit SQL Server 2005 SP2 on a Windows 2003 x64. The developer said that he needed to use OLE to talk to Oracle so I went to Oracle and downloaded the 64 bit OraOLDDB driver as recommended in a MS note.
I can connect to Oracle via tnsping and using SQLPlus but I cannot create a linked db successfully.
The linked database errors off with >> Cannot initialize data source for OLE provider "OraOLEDB.Oracle" for Lined Server "X" (Microsoft SQL Server error: 7399) << (manually typed)
Here is the official OS, SQL Server, and Oracle client information:
The OS is Windows Server 2003 Standard x64 edition Service Pack 2 (NT 5.2 build 3790)
The SQL Server 2005 9.0.3042 (x64) [from
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00
(srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.3959
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
The OraOLEDB.Oracle driver shows that it is version 10.2.0.3
Oracle "tnsping sid " works
SQLPlus (ver 9.2) works
The driver showed as a provider in SQL Server 2005 as soon as the install was done and I then updated the registry for 3 entries to point to the driver. But I notice that nothting shows in the ODBC management screen for the driver, should it? The only 2 drivers that show are from MS and one is for 32 bit SQL Server and the other is the newer MS SQL Server driver.
I have serveral linked databases to Oracle that I have built but all of them are 1- on SQL Server 2000 and 2- are 32 bit. If anyone knows of any additional patches (Windows or Oracle) necessary to resolve this I would appreciate the information. Also there are potential firewall issues though the fact that SQLPus can be used seems to rule this out, but if there are any ports associated with OLE I would like to check on them specifically.
MSDTC is installed and appears configured correctly.
-- Mark D Powell --
November 13, 2007 at 11:10 pm
YOu try reinstalling the MDAC component and then try you should be seeing all the drivers for linked servers with the MDAC version newly installed.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 14, 2007 at 6:12 am
I have a 64 bit SQL Server 2005 SP2 on a Windows 2003 x64. The developer said that he needed to use OLE to talk to Oracle so I went to Oracle and downloaded the 64 bit OraOLDDB driver as recommended in a MS note.
Microsoft does not make a 64 MDAC componennt for Oracle and they won't be making one. You will need to download and install the ODAC (Oracle Data Access Component) from the Oracle site as IanT indicated (with the link).
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 14, 2007 at 7:36 am
Mg, it is the recommended download which is not working. Oracle has updated the download (.3 now instead of .2 in the note)
What I need is a list of settings to verify at the OS level, registry (added or changed 3 entries under MSDTC) etc... that I can verify.
Also any information on how to tell that the firewall which I have no access to isn't the problem.
Suggestion on how to find the actual problem also welcomed.
What I really need is for someone who set up and uses the 64 but OraOLEDB to tell me that what I should should work or provide an also you need this.
-- Mark D Powell --
November 14, 2007 at 8:47 am
OK - I did this on 3 64 bit servers and they all work.
1 - install the 10g client (.3 version is what I installed)
2 - install the ODAC package (this is not part of the client)
3 - validate the TNSNAMES.ORA and SQLNET.ORA - be sure the registry entry points to where the files actually are (since you can connect with SQLPLus, I guess they are)
4 - stop/start SQL
5 - the Linked Servers/Providers should now have "OraOLEDB.Oracle" in the list.
6 - create linked servers to Oracle
a. the Provider drop down should now include "Oracle Provider for OLE DB"
I don't know if the client is really needed since I can't find a registry entry for TNS_ADMIN in the 10g keys but rather than take chance, I've installed it since I don't have any place to test my theory.
Links are working for me. If you have firewall issue, I can't offer any advice.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply