Hello there.
I have read a lot of discussion and documentation about it but any of the things I'm trying are working.
I want to connect from Oracle to SQLServer. I have made the following steps:
(Taking into account I want to connect to a database SQLSERVER called GS2PIASQL03\INSTANCE3)
Create odbc connection in the server where oracle is running called GS2PIASQL03
Name: GS2PIASQL03
Description: GS2PIASQL03
Server: GS2PIASQL03\INSTANCE3
With sql server authentication using a login ID and passwotrd entered by the user
Connect to sql server to obtain default settings for the additional configuration option
User: rema_read
Password: xxxxxxx
Default database master
Use ANSI quoted identifiers
Use ANSI nulls, paddings and warnings
Perform translation for character data
Finish and testing the data source before TESTS COMPLETED SUCCESSFULLY.
Content of LISTENER.ora
# listener.ora Network Configuration File: S:\app\oracle\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 = S:\app\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:S:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = CRM)
(ORACLE_HOME = S:\app\oracle\product\11.2.0\tg_1)
(PROGRAM = S:\app\oracle\product\11.2.0\tg_1\bin\dg4odbc.exe)
)
(SID_DESC =
(SID_NAME = CRMSVC)
(ORACLE_HOME = S:\app\oracle\product\11.2.0\tg_1)
(PROGRAM = dg4odbc)
)
(SID_DESC=
(GLOBAL_DBNAME=REMA)
(ORACLE_HOME=S:\app\oracle\product\11.2.0\dbhome_1)
(SID_NAME=REMA)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SLPIAM29)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = S:\app\oracle
Content of TNSNAMES.ORA
# tnsnames.ora Network Configuration File: s:\app\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
GS2PIASQL03 =
(DESCRIPTION=
(ADDRESS = (PROTOCOL=tcp)
(HOST = 172.16.61.18)
(PORT = 1521)
)
(CONNECT_DATA = ( SID = GS2PIASQL03 ))
(HS = OK)
)
Content of initGS2PIASQL03.ora
HS_FDS_CONNECT_INFO = GS2PIASQL03
#HS_FDS_SHAREABLE_NAME = C:\Windows\System32\odbc32.dll
HS_FDS_TRACE_LEVEL = Debug
Stop and start listener then create a database link executing the next command in toad:
CREATE PUBLIC DATABASE LINK TEST_SQLS
CONNECT TO "read_rema"
IDENTIFIED BY "password"
USING 'GS2PIASQL03'
/
When I test the database link (with the toad option) or run a query like:
select * from sys.sysobjects@TEST_SQLS;
I obtain the same error
*********************************************************************
Link : "TEST_SQLS"
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_SQLS
*********************************************************************
Please I really need your help I really really need this to work and I'm completely lost know.
I'm looking forward to hearing from you soon.
Vinny.