Solved

Oracle Connect to SQL Server via heterogeneous Services

Posted on 2006-07-10
14
7,264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

690 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