1

I have tried to access a table which is sqlserver from oracle. But I got following error message.

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

Configuration Details :

F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\hs\admin\initSQLEXPRESS.ora

HS_FDS_CONNECT_INFO = SQLEXPRESS
HS_FDS_TRACE_LEVEL = TRUE
HS_RPC_FETCH_REBLOCKING= OFF
HS_FDS_FETCH_ROWS = 1
HS_TRANSACTION_MODEL=SINGLE_SITE
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.UTF8

F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN*listener.ora

# listener.ora Network Configuration File: F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = F:\app\administrator\oracle11g\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = SQLEXPRESS)
      (ORACLE_HOME = F:\app\administrator\oracle11g\product\11.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
    )   
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SHUAPP01.SHU.intern)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SHUAPP01)(PORT = 1443))        
    )
  )

ADR_BASE_LISTENER = F:\app\administrator\oracle11g

F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\NETWORK\ADMIN*tnsnames.ora

# tnsnames.ora Network Configuration File: F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SHUAPP01.SHU.intern)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

SQLEXPRESS =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL = TCP)(HOST = SHUAPP01)(PORT = 1433))
      (CONNECT_DATA=(SID=SQLEXPRESS))
      (HS=OK)
    )  

Oracle Link

CREATE DATABASE LINK SQLSERVER1 CONNECT TO "usrname" IDENTIFIED BY "password" USING 'SQLEXPRESS';

ODBC Driver config and test results

Microsoft SQL Server ODBC Driver Version 10.00.14393

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

Microsoft SQL Server ODBC Driver Version 10.00.14393

Data Source Name: SQLEXPRESS
Data Source Description: TESTING 
Server: SHUAPP01
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: Yes
Statistics File: C:\Users\admin.vitalmed.dba\Desktop\STATS.LOG
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

Please help me to overcome this error.

Thanks

lsnrctl start

TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to f:\app\administrator\oracle11g\diag\tnslsnr\SHUAPP01\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SHUAPP01.SHU.intern)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SHUAPP01.SHU.intern)(PORT=1443)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                16-JUN-2017 14:54:48
Uptime                    0 days 0 hr. 0 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         f:\app\administrator\oracle11g\diag\tnslsnr\SHUAPP01\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SHUAPP01.SHU.intern)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SHUAPP01.SHU.intern)(PORT=1443)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SQLEXPRESS" has 1 instance(s).
  Instance "SQLEXPRESS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
  • If you make a tnsping sqlexpress, it works? What is the statement you're executing? – Renato Afonso Jun 16 '17 at 12:23
  • @RenatoAfonso This is what I get. Please forgive me as I am a noob here, Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL = TCP)(HOST = SHUAPP01)(PORT = 1433)) (CONNECT_DATA=(SID=SQLEXPRESS)) (HS=OK)) TNS-12541: TNS:no listener – User12345 Jun 16 '17 at 12:34
  • @RenatoAfonso F:\app\administrator\oracle11g\product\11.2.0\dbhome_1\BIN>lsnrctl services SQLEXPRESS LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 16-JUN-2017 14:41:40 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)>>(HOST=SHUAPP01)(PORT=1433))(CONNECT_DATA=>>(SID=SQLEXPRESS))(HS=OK)) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener 64-bit Windows Error: 61: Unknown error – User12345 Jun 16 '17 at 12:44
  • Ok. Try and start the listener: "lsnrctl start", just to check what's the output? – Renato Afonso Jun 16 '17 at 12:53
  • @RenatoAfonso I have added the output in question section. Please have a look. – User12345 Jun 16 '17 at 12:59
1

Your listener is listening on port 1443, but your tnsnames specifies port 1433. So the error is correct ... there is no listener on the requested port of 1433.

Why the separate port, anyway? The listener can handle everything on port 1521. Notice that there is nothing in the listener.ora or any indication in the output of lsnrctl that suggests the listener has any binding of a particular port to a particular instance or service.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.