Solved

Oracle Connect to SQL Server via heterogeneous Services

Posted on 2006-07-10
14
7,199 Views
Last Modified: 2008-01-09
Folks,

This is actually a little different than the last question i asked which was a listener issue.  This may be as well.  however i had to scrap the last method as HS doesnt support 64 bit ODBC.  I am trying to make a connection to sql server via heterogeneous Services with Oracle 10.2 database and also OWB 10.2.  I have followed the tutorial on http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1
Scenario
windows server 2003 with oracle 10.2 database and owb 10.2 in different homes
listener etc in the following
db =D:\oracle\product\10.2.0\rdbms\NETWORK\ADMIN
owb = E:\oracle\product\owb\network\ADMIN
sql server on machine B which is a standalone server so i must use uid/pwd to connect
odbc connection to sql server systemdsn name = "mysqlserverdsn" test = "TESTS COMPLETED SUCCESSFULLY!"


I have the same files in 4 places
OWB path
D:\oracle\product\10.2.0\rdbms\NETWORK\ADMIN
D:\oracle\product\10.2.0\rdbms\hs\admin
DB Path
E:\oracle\product\owb\network\ADMIN
E:\oracle\product\owb\hs\admin

INITMYSQLSERVERDSN.ORA =
      HS_FDS_CONNECT_INFO = MYSQLSERVERDSN
      HS_FDS_TRACE_LEVEL  = OFF

LISTENER.ORA =
      SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = d:\oracle\product\10.2.0\rdbms)
            (PROGRAM = extproc)
          )
        )
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            (ADDRESS = (PROTOCOL = TCP)(HOST = srv-winora.bob.org)(PORT = 1521))
          )
      
        )
      
      LISTENERMYSQLSERVERDSN =
       (ADDRESS_LIST=
            (ADDRESS=(PROTOCOL=tcp)(HOST=srv-winora.bob.org)(PORT=1522))
            (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
      
      SID_LIST_LISTENERMYSQLSERVERDSN=
        (SID_LIST=
            (SID_DESC=
               (SID_NAME=MYSQLSERVERDSN)
               (ORACLE_HOME = E:\oracle\product\owb)
               (PROGRAM=hsodbc)
             )
         )


SQLNET.ORA
      SQLNET.AUTHENTICATION_SERVICES= (NTS)
      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
      
      
TNSNAMES.ORA
      RDBMS =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = srv-winora.bob.org)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = rdbms)
          )
        )
      
      OWBREPO =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = srv-winora.bob.org)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = owbrepo.bob.org)
          )
        )
      
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      
      MYSQLSERVERDSN  =
        (DESCRIPTION=
          (ADDRESS=(PROTOCOL=tcp)(HOST=srv-winora.bob.org)(PORT=1522))
          (CONNECT_DATA=(SID=MYSQLSERVERDSN))
          (HS=OK)
        )

so i have the same 4 files in each of the above directories.
and i stop and start my listeners.
>lsnrctl stop
>lsnrctl stop listenermysqlserverdsn
>lsnrctl start
>lsnrctl start listenermysqlserverdsn

try a tns ping
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=srv-winora
.bob.org)(PORT=1522)) (CONNECT_DATA=(SID=MYSQLSERVERDSN)) (HS=OK))
OK (20 msec)


start sql plus
connect sys@rdbms as sysdba
... connected
SQL>create database link mysqlserverdsn
 connect to sa identified by pwd using 'MYSQLSERVERDSN'

Database link created.

SQL> desc vehicle@mysqlserverdsn
ERROR:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYSQLSERVERDSN

I try the same thing with
connect sys@owbrepo as sysdba
and get the same results

recap
machine A windows server 2003 with oracle 10.2 database and owb 10.2 in different homes
listener etc in the following
db =D:\oracle\product\10.2.0\rdbms\NETWORK\ADMIN
owb = E:\oracle\product\owb\network\ADMIN
sql server on machine B which is a standalone server so i must use uid/pwd to connect
odbc connection to sql server systemdsn name = "mysqlserverdsn" test = "TESTS COMPLETED SUCCESSFULLY!"
TNSPING ok
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYSQLSERVERDSN

I have searched high and low and the oracle errors point me to a tns ping which works.
One thing i ddn't do is set any path variables which may be the whole issue.  

however i executed sqlplus from each home directory/bin

Thanks,

Steve
0
Comment
Question by:stevetheski
  • 6
  • 6
  • 2
14 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17072033
The following link contains "Worked example for using Excel through ODBC". The same thing can be implemented for connecting SQL Server with Oracle.
http://asktom.oracle.com/pls/ask/f?p=4950:8:3250259327871965997::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4406709207206#18830681837358
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17072617
0
 
LVL 4

Author Comment

by:stevetheski
ID: 17073341
both links give directions basically the same as the link i followed.  That is no help.  I included my tnsnames listner and other .ora files which is exactly what the references you all and I have posted.  

after tracinfg a few log files i have found the error
TNS-12500: TNS:listener failed to start a dedicated server process
 TNS-12560: TNS:protocol adapter error
  TNS-00530: Protocol adapter error
   32-bit Windows Error: 2: No such file or directory

in my listener log file

global names parameter is set to false
have looked at
https://metalink.oracle.com/metalink/plsql/f?p=130:14:9147614468411078163::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,234517.1,1,1,1,helvetica

I am not looking for someone to paste a link that tells one how to connect oracle to sql server as that is there.  I am looking for someone to review what I have in my files and see if there is something that i may have implemented incorrectly.  

Thanks

0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17075846
Try preceding the DSN name with the name of the ODBC drive in your INITMYSQLSERVERDSN.ORA and see if that helps
0
 
LVL 4

Author Comment

by:stevetheski
ID: 17084916
still i can tns ping but no connection
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYSQLSERVERDSN

i also have tried connection to sql server from oracle warehouse builder on the same machine and get the same error if i use port/host/service is it something with 2003 64 bit?  

any help would be appreciated
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17084949
Check if your sql server is up and running? Also check if you can connect to the sql server directly
0
 
LVL 4

Author Comment

by:stevetheski
ID: 17086238
yes i can connect to sql server through enterprise manager, odbc config tool, vb and c#.  I am sure it is something stupid like that.  One thing i did notice is that my listener.ora file has the following line
              (PROGRAM=hsodbc)
however I cannot find such a file anywhere other than the inithsodbc.ini
if you use hetero svs do you have such a file?


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Accepted Solution

by:
sathyagiri earned 400 total points
ID: 17086342
hsodbc should be under your $ORACLE_HOME\server folder
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17086400
Check if the hs services data dictionary views exits. Try doing a desc SYS.HS_FDS_CLASS.
If not run the caths.sql under the $ORACLE_HOME\rdbms\admin folder
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 100 total points
ID: 17087623
In listener.ora file ORACLE_HOME is different

>>           (ORACLE_HOME = d:\oracle\product\10.2.0\rdbms)

>>              (ORACLE_HOME = E:\oracle\product\owb)




Check the following line. It should be extproc
>>        (PROGRAM = extproc1)


TNSPING MYSQLSERVERDSN

$ tnsping MYSQLSERVERDSN


Check listener services

$ lsnrctl services

$ lsnrctl status

0
 
LVL 4

Author Comment

by:stevetheski
ID: 17087942
ok i did a desc on the desc SYS.HS_FDS_CLASS
it was there but i ran caths.sql again anyway
I did not see an hsodbc file anywhere
on my xp machine it is in D:\oracle\product\10.2.0\db_1\BIN
i think we may be getting something here
does anyone know what package i need to select to install the file through OUI?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17090063
I think u need to install the generic connectivity through ODBC pack.
Check this link for details
http://www.minisoft.com/pages/middleware/ODBC_UNIX/odbc_for_oracle.htm
0
 
LVL 4

Author Comment

by:stevetheski
ID: 17094822
Ok I am a retard.  Even though I stated at the begining that hsodbc doesnt work with 64 bit odbc which is why i switched from linux to win for this install I actually installed 10g_win64. I will split up the points after my nap :)


does anyone have the link handy for the certified platforms for HSODBC?
i think that would be nice to have on this q.

Thanks
0
 
LVL 4

Author Comment

by:stevetheski
ID: 17177402
there is another answer to the problem.  hsodbc is not supported on xp or 2003
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 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

9 Experts available now in Live!

Get 1:1 Help Now