ORA-28545 ... Unable to retrieve text of NETWORK/NCR message 65535, Error trying to use ODBC to access SQL Server from Oracle Options

[Rene]

Hi everyone,

I am trying to retrieve data from a remote SQL database using a dblink in my Oracle DB. I successfully managed to make it work on my development db running on RedHat, but now I am trying to do exactly the same on my production db running on HP-UX. I am using unixODBC and freetds to access the remote database. I have correctly compiled and configured unixODBC, as I can retrieve data from isql (the unixODBC sql application). I have configured listener.ora and tnsnames.ora in $ORACLE_HOME/network/admin and I have created an initMSSQLIISDB.ora in $ORACLE_HOME/hs/admin. I attached this files at the bottom. I also created the dblink and restarted the listener. The problem is that when I try to retrieve data using the dblink with a simple select statement I get the following error:

select count(*) from SDC_Cuerpo@MSSQLIISDB

ORA-28545: error diagnosticado por Net8 al conectar a un agente
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: 2 lines precediendo a MSSQLIISDB


I have been trying to find out what's wrong. I have checked the HS_FDS_INST and there's no instance referenced there, so I think the problem is that the odbc is not registered. As far as I know, it should autoregister. Any suggestion?


Thanks in advance and best regards.

Rene.


initMSSQLIISDB.ora


#Fichero de configuracion del servicio HS para conexion entre
#base de datos oracle y la base de datos SQL Server MSSQLIISDB
#(IISDB en serverdbeduca)
#
# HS init parameters
#

HS_FDS_CONNECT_INFO = MSSQLIISDB
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = MSSQLIISDB.log
HS_LANGUAGE = SPANISH_SPAIN.WE8ISO8859P1
HS_FDS_SHAREABLE_NAME = /usr/local/unixODBC/lib/libodbc.sl
HS_AUTOREGISTER = true

#
# ODBC specific environment variables
#
set ODBCINI = /usr/local/unixODBC/etc/odbc.ini




listener.ora


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME = gipd)
(ORACLE_HOME = /u/oracle/oracle/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = MSSQLIISDB)
(ORACLE_HOME = /u/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = hsodbc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rigel)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
)







tnsnames.ora


GIPD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rigel)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gipd)
)
)

GIPDVM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle10desavm.educacion.org)(PORT = 152
1))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = gipdvm)
)
)

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

# SID para la conexion con un odbc.
MSSQLIISDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =rigel)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MSSQLIISDB)
)
(HS=OK)
)

[burleson]

Hi,

This is a Net8 error! What release are you on?

ORA-28545: error diagnosed by Net8 when connecting to an agent

Cause: An attempt to call an external procedure or to issue SQL to a non-Oracle system on a Heterogeneous Services database link failed at connection initialization. The error diagnosed by Net8 NCR software is reported separately.

Action: Refer to the Net8 NCRO error message. If this isn't clear, check connection administrative setup in tnsnames.ora and listener.ora for the service associated with the Heterogeneous Services database link being used, or with 'extproc_connection_data' for an external procedure call.

I have some of my notes here:

http://www.dba-oracle.com/t_database_link_...rver_oracle.htm

http://www.dba-oracle.com/t_heterogeneous_..._sql_server.htm

1. Install Oracle ODBC drivers on the server (your local Oracle database) that will access the remote SQL Server database using the database link.

2. Setup the ODBC connection on the local Oracle database using the Windows ODBC Data Source Administrator

3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.

4. Ensure that your global_names parameter is set to False.

5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

7. Modify the Listener.ora file.

SID_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells Oracle to use heterogeneous services.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=Cas30C) -- Enter the DSN on this line
(ORACLE_HOME = c:\oracle10gdb) -- Enter your Oracle home on this line
(PROGRAM = hsodbc) ) )


8. Modify the Tnsnames.ora file. This is the Oracle database installation accessed by the users to connect to the ODBC drivers

(DESCRIPTION=
(ADDRESS_LIST=
(Address=(PROTOCOL=TCP)
(HOST=
-- (Server x)
(PORT=1521))) -- Enter the port on which the server x Oracle installation
-- is listening
(CONNECT_DATA=(SID=Cas30c)) - Enter the DSN name
(HS=OK) -- Enter this value. It tells Oracle to use hetergeneous services
)

9. Reload the listener on local Oracle database

10. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.

11. Run a SQL Server Select statement from the Oracle installation using the database link.