deNZity
asked on
problem creating link to Oracle db
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\t nsnames.or a) 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.
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\
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.
can you connect using SQL PLus?
ASKER
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"
entering sqlplus at commandline prompt returns "sqlplus is not recognized as an internal or external command"
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)
ASKER
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.
could not resolve the connect identifier specified
so the tnsnames.ora file in the client directory cannot be getting read.
post your TNSMNames.ora file back... thanks
ASKER
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))
)
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))
)
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?
can you ping the servers WDEL1, WORA2
Can you TNSPing them and see what error do you get?
ASKER
can ping both servers from sql box
tnsping returns "tnsping is not recognized as an internal or external command"
tnsping returns "tnsping is not recognized as an internal or external command"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Tnsping should be installed by default...
<drive>:\Oracle\...\bin\tn sping.exe
<drive>:\Oracle\...\bin\tn
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\SOFTWA RE [HKEY_LOCAL_MACHINE\SOFTWA RE
\Microsoft\Transaction Server \Microsoft\MSDTC\MTxOCI]
\Local Computer\My Computer] "OracleXaLib"="oraclient10 .dll"
"OracleXaLib"="oraclient10 .dll" "OracleSqlLib"="orasql10.d ll"
"OracleSqlLib"="orasql10.d ll" "OracleOciLib"="oci.dll"
"OracleOciLib"="oci.dll"
Be careful with the registry changes
for 10g [HKEY_LOCAL_MACHINE\SOFTWA
\Microsoft\Transaction Server \Microsoft\MSDTC\MTxOCI]
\Local Computer\My Computer] "OracleXaLib"="oraclient10
"OracleXaLib"="oraclient10
"OracleSqlLib"="orasql10.d
"OracleOciLib"="oci.dll"
ASKER
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
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
ASKER
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=152 1)))
Message 3509 not found; No message file for product=NETWORK, facility=TNS
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
OCOL=TCP)(HOST=172.30.1.80
Message 3509 not found; No message file for product=NETWORK, facility=TNS
The error you got is related to the registry keys I mentioned.. change them and restart your machine
ASKER
All I have under transaction server is setup
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\Transact ion Server\Setup
these following keys exist
[HKEY_LOCAL_MACHINE\SOFTWA RE
\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient10 .dll"
"OracleSqlLib"="orasql10.d ll"
"OracleOciLib"="oci.dll"
HKEY_LOCAL_MACHINE\SOFTWAR
these following keys exist
[HKEY_LOCAL_MACHINE\SOFTWA
\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient10
"OracleSqlLib"="orasql10.d
"OracleOciLib"="oci.dll"
then you are alright, did you restart your machine after the Oracle client installation? OCX will not initiate untill you restart.
ASKER
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=152 1)))
Message 3509 not found; No message file for product=NETWORK, facility=TNS
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
OCOL=TCP)(HOST=172.30.1.80
Message 3509 not found; No message file for product=NETWORK, facility=TNS
ASKER
Should also mention that I can connect to the database from sqlplus
ASKER
The operating system is windows server 2003
ASKER
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
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