Solved

problem creating link to Oracle db

Posted on 2006-07-11
20
642 Views
Last Modified: 2008-03-17
Hi,

I have installed the Oracle 10g client 10.2 on the sql server; placed tnsnames.ora file in oracle directory and created
TNS_ADMIN enviroment variable with value (D:\oracle\product\10.2.0\client_1\tnsnames.ora) but when I try to create
system dsn the drop down box shows no entries forTNS Service Name so a connection cannot be setup.
My understanding is if I can't do this then I cannot create a link to the Oracle box.
0
Comment
Question by:deNZity
  • 11
  • 8
20 Comments
 
LVL 12

Expert Comment

by:Einstine98
ID: 17086328
can you connect using SQL PLus?
0
 

Author Comment

by:deNZity
ID: 17086423
The only tool in the oracle program files is the microsoft odbc Administrator and some ole help files
entering sqlplus at commandline prompt returns "sqlplus is not recognized as an internal or external command"
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17086451
try installing the client again checking the options... you should first do the connection through SQLPLUS once that's done and it's working fine... you need to change certain registry keys (I will look them up for you) to get OLEDB to work (aka linked servers)
0
 

Author Comment

by:deNZity
ID: 17086495
I installed sqlplus and tried to connect to the oracle db and got...

could not resolve the connect identifier specified

so the tnsnames.ora file in the client directory cannot be getting read.
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17086515
post your TNSMNames.ora file back... thanks
0
 

Author Comment

by:deNZity
ID: 17086575
tnsnames.ora

ghwtst =
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = wora2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ghwtst)
    )
 )

ghwprd =
 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = wora2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ghwprd)
    )
 )

ECLR =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = wdel1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ECLR))
  )

ECLT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = wdel1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ECLT))
  )
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17086586
out these four databases, are you unable to connect to any?

can you ping the servers WDEL1, WORA2

Can you TNSPing them and see what error do you get?
0
 

Author Comment

by:deNZity
ID: 17086674
can ping both servers from sql box
tnsping returns  "tnsping is not recognized as an internal or external command"
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 17086737
check the article it is for O9i but can give you idea:
Linked Severs on MS SQL Part 4, Oracle
http://www.databasejournal.com/features/mssql/article.php/3290801

also:
How to set up and troubleshoot a linked server to an Oracle database in SQL Server
http://support.microsoft.com/kb/280106/
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17086747
Tnsping should be installed by default...

<drive>:\Oracle\...\bin\tnsping.exe
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 12

Expert Comment

by:Einstine98
ID: 17086758
Thanks EugeneZ I was looking for that MS KB to help him out but coudln't find it...

Be careful with the registry changes

for 10g [HKEY_LOCAL_MACHINE\SOFTWARE     [HKEY_LOCAL_MACHINE\SOFTWARE
           \Microsoft\Transaction Server    \Microsoft\MSDTC\MTxOCI]
           \Local Computer\My Computer]     "OracleXaLib"="oraclient10.dll"
           "OracleXaLib"="oraclient10.dll"   "OracleSqlLib"="orasql10.dll"  
           "OracleSqlLib"="orasql10.dll"     "OracleOciLib"="oci.dll"
           "OracleOciLib"="oci.dll"
0
 

Author Comment

by:deNZity
ID: 17086889
Thanks Eugene I will read the articles. Einstein there is no tnsping.exe in the bin folder.

Have done a custom reinstall of both client and the oracle windows interfaces.
I now have  network\ADMIN\ folder so have put both tnsnames.ora and sqlnet.ora
files in this folder and removed the TNS_ADMIN enviroment variable.

Was able to create an odbc connection "YAY" TNS Service Name drop down had the relevant servers.
created the link but get an error message 7399 OLE DB Provider 'Microsoft.jet.oledb.4.0' IDBInitialize::Initialize returned ox80004005
0
 

Author Comment

by:deNZity
ID: 17086941
copied tnsping.exe from other oracle directory on differnet pc to oracle bin directory on sql server.

C:\Documents and Settings\RobeMorg>tnsping wora2

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-JUL-2
006 12:59:14

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage
 3512 not found; No message file for product=NETWORK, facility=TNSAttempting to
contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=wora2.ghw.co.nz))(ADDRESS=(PROT
OCOL=TCP)(HOST=172.30.1.80)(PORT=1521)))
Message 3509 not found; No message file for product=NETWORK, facility=TNS
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17086954
The error you got is related to the registry keys I mentioned.. change them and restart your machine
0
 

Author Comment

by:deNZity
ID: 17087187
All I have under transaction server is setup

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Transaction Server\Setup

these following keys exist
[HKEY_LOCAL_MACHINE\SOFTWARE
             \Microsoft\MSDTC\MTxOCI]
             "OracleXaLib"="oraclient10.dll"
             "OracleSqlLib"="orasql10.dll"  
               "OracleOciLib"="oci.dll"
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17087209
then you are alright, did you restart your machine after the Oracle client installation? OCX will not initiate untill you restart.
0
 

Author Comment

by:deNZity
ID: 17087251
Hi Einstine98, rebooted server and tried tnsping again with same result

Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage
 3512 not found; No message file for product=NETWORK, facility=TNSAttempting to
contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=wora2.ghw.co.nz))(ADDRESS=(PROT
OCOL=TCP)(HOST=172.30.1.80)(PORT=1521)))
Message 3509 not found; No message file for product=NETWORK, facility=TNS
0
 

Author Comment

by:deNZity
ID: 17087530
Should also mention that I can connect to the database from sqlplus
0
 

Author Comment

by:deNZity
ID: 17087616
The operating system is windows server 2003
0
 

Author Comment

by:deNZity
ID: 17135196
solution was to change...
sp_addlinkedserver 'server', 'Oracle', 'MSDAORA', 'server'
sp_addlinkedsrvlogin 'server', false, 'sa', 'UID','PWD'

to

sp_addlinkedserver 'server', 'Oracle', 'MSDAORA', 'server'
sp_addlinkedsrvlogin 'server', false, null, 'UID','PWD'

D
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now