Home » RDBMS Server » Networking and Gateways » how to connect oracle forms with ms sql server 2008 (windows server 2003 R2 enterprise x64 edition service pack 2,Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
how to connect oracle forms with ms sql server 2008 [message #612537] Mon, 21 April 2014 05:43 Go to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
here i did steps i want to connect my oracle database with ms sql server 2008

first of all i create  " initsqlserver1.ora file at "C:\Oracle\product\10.2.0\db_1\hs\admin"



# ODBC DSN
HS_FDS_CONNECT_INFO = sqlserver1
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SUPPORT_STATISTICS=FALSE 


after that i have edited my listener.ora file my listener.ora file is look this

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )
(SID_DESC =
      (SID_NAME = sqlserver1)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = hsodbc)
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tap)(PORT = 1522))
    )
  )



after that i have editied my " tnsnames.ora " file


# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

GREEN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = tap)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = green)
    )
  )

BLACK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = torcl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = black)
    )
  )

LISTENER_GREEN =
  (ADDRESS = (PROTOCOL = TCP)(HOST = tap)(PORT = 1522))


EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

sqlserver1 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=tsqlserver)(PORT=1521))
      (CONNECT_DATA=(SID=sqlserver1))
      (HS=OK)
    )



after that reload lsnrctl

C:\Documents and Settings\Administrator>lsnrctl reload

LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 21-APR-2014 16:02
:57

Copyright (c) 1991, 2006, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully

C:\Documents and Settings\Administrator>


after that i create database link


SQL> create database link test using 'SQLSERVER1';

Database link created.



NOW when i connect with my sql server i got this error

SQL> conn su@odbc:sqlserver1
Enter password:
ERROR:
ORA-03121: no interface driver connected - function not performed


SQL> select * from tab@sqlserver1
  2  /
select * from tab@sqlserver1
                  *
ERROR at line 1:
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 sqlserver1


please any one give me steps of how to connect oracle database with ms sqlserver 2008
Re: how to connect oracle forms with ms sql server 2008 [message #612542 is a reply to message #612537] Mon, 21 April 2014 10:31 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The very first topic you saw when creating yours was about exactly the same problem, so I'll just copy here what I wrote there.

Have a look at this Maaher's walkthrough. I hope it'll help.
Re: how to connect oracle forms with ms sql server 2008 [message #612586 is a reply to message #612542] Tue, 22 April 2014 05:28 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
thanks for your reply little foot but i already read it this before and i follow all this steps still i am getting this error


LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tap)(PORT=1522)))
TNS-01192: Missing SID_LIST_ value left of equation for SID description in LISTE
NER.ORA

Listener failed to start. See the error message(s) above...

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tap)(PORT=1522)))
TNS-01192: Missing SID_LIST_ value left of equation for SID description in LISTE
NER.ORA

Listener failed to start. See the error message(s) above...

LSNRCTL>



# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )
(SID_DESC =
      (SID_NAME = sqlserver1)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = hsodbc)
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tap)(PORT = 1522))
    )
  )




SQL> desc emp@sqlserver1
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 sqlserver1

Re: how to connect oracle forms with ms sql server 2008 [message #612588 is a reply to message #612586] Tue, 22 April 2014 06:10 Go to previous messageGo to next message
John Watson
Messages: 4570
Registered: January 2010
Location: Global Village
Senior Member
Look at your listener.ora file. In particular, check the brackets in your SID_LIST_LISTENER section.
Re: how to connect oracle forms with ms sql server 2008 [message #612589 is a reply to message #612588] Tue, 22 April 2014 06:37 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
thanks for you reply john

i am sending you my listener.ora file here


# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )
(SID_DESC =
      (SID_NAME = sqlserver1)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = hsodbc)
    )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tap)(PORT = 1522))
    )
  )



LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tap)(PORT=1522)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tap)(PORT=1522)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error
LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tap)(PORT=1522)))
TNS-01192: Missing SID_LIST_ value left of equation for SID description in LISTE
NER.ORA

Listener failed to start. See the error message(s) above...

LSNRCTL>


can you please suggest me

[Updated on: Tue, 22 April 2014 06:40]

Report message to a moderator

Re: how to connect oracle forms with ms sql server 2008 [message #612590 is a reply to message #612588] Tue, 22 April 2014 06:39 Go to previous messageGo to next message
John Watson
Messages: 4570
Registered: January 2010
Location: Global Village
Senior Member
John Watson wrote on Tue, 22 April 2014 12:10
Look at your listener.ora file. In particular, check the brackets in your SID_LIST_LISTENER section.

Re: how to connect oracle forms with ms sql server 2008 [message #612591 is a reply to message #612590] Tue, 22 April 2014 06:58 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
johson i checked it already all brackets are correct you can also see this i already past it here this my listener.ora file
Re: how to connect oracle forms with ms sql server 2008 [message #612592 is a reply to message #612591] Tue, 22 April 2014 06:59 Go to previous messageGo to next message
John Watson
Messages: 4570
Registered: January 2010
Location: Global Village
Senior Member
No, the brackets are not right. Look at your SID_LIST and your SID_DESCs.
Re: how to connect oracle forms with ms sql server 2008 [message #612824 is a reply to message #612592] Fri, 25 April 2014 07:17 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
please help me in this john i did all this step as you give me this link

but still i am getting this error

C:\oracle\product\10.2.0\db_1\hs\admin\inittest.ora


# HS init parameters
#
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = 0


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>



C:\oracle\product\10.2.0\db_1\network\ADMIN\listener.ora


# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = test)
      (program = hsodbc)
  (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )




C:\oracle\product\10.2.0\db_1\network\ADMIN\tnsnames.ora

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
test =
   (description =
    (address = (protocol=tcp)(host=test-av)(port=1521))
     (connect_data = (sid=test))
     (hs=ok)
   )


EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )




SQL> create database link test using 'TEST';

Database link created.



C:\oracle\product\10.2.0\db_1\bin>SQLPLUS

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 25 17:39:16 2014

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Enter user-name: SYS AS SYSDBA
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> DESC EMP@TEST
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 TEST


SQL>



C:\Documents and Settings\Administrator>TNSPING TEST

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 25-APR-2
014 17:43:27

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

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol=tcp)(host=test-av)(port=1521)) (connect_data = (sid=gidc)) (hs=ok))
TNS-12541: TNS:no listener

C:\Documents and Settings\Administrator>


Re: how to connect oracle forms with ms sql server 2008 [message #612827 is a reply to message #612824] Fri, 25 April 2014 07:58 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
What is hostname and IP# of system where SQL SERVER runs?
What is hostname and IP# of system where Oracle DB runs?
Re: how to connect oracle forms with ms sql server 2008 [message #612969 is a reply to message #612827] Mon, 28 April 2014 07:11 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
thanks for reply
BlackSwan


system where SQL SERVER runs

hostname    test-av
 and IP#    192.168.10.7
   



system where Oracle DB runs

hostname    torcl
 and IP#    192.168.10.10
Re: how to connect oracle forms with ms sql server 2008 [message #612985 is a reply to message #612969] Mon, 28 April 2014 08:09 Go to previous messageGo to next message
BlackSwan
Messages: 22793
Registered: January 2009
Senior Member
>(HOST = localhost)
you need to be consistent regarding hostname where Oracle resides
is above or below correct?
>hostname torcl
Re: how to connect oracle forms with ms sql server 2008 [message #613044 is a reply to message #612985] Tue, 29 April 2014 02:10 Go to previous messageGo to next message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
thanks again blackswan
ok hostname is torcl now where i install my oracle db i am sending you listiner file also here please help me to modify my listiner file



LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or
a
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=torcl)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=torcl)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ
ction
Start Date                29-APR-2014 11:13:31
Uptime                    0 days 0 hr. 0 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File         C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=torcl)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 2 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>



C:\Documents and Settings\Administrator>TNSPING TEST

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 29-APR-2
014 11:15:35

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

Used parameter files:
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol=tcp)(host=test-av)(por
t=1521)) (connect_data = (sid=test)) (hs=ok))
TNS-12541: TNS:no listener

C:\Documents and Settings\Administrator>



# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = test)
      (program = hsodbc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = test)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = torcl)(PORT = 1521))
  )


SQL> DESC EMP@TEST
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 TEST

Re: how to connect oracle forms with ms sql server 2008 [message #613374 is a reply to message #613044] Tue, 06 May 2014 00:18 Go to previous message
x-oracle
Messages: 332
Registered: April 2011
Location: gujarat
Senior Member
no any reply..... Sad Sad
Previous Topic: connect sql database from oracle
Next Topic: Read Data from MS Sql Server - Oracle Client
Goto Forum:
  


Current Time: Mon Sep 22 00:02:24 CDT 2014

Total time taken to generate the page: 0.09510 seconds