Re: Heterogeneous Service Setup: SQL Server link fails

comments embedded...

Newbie wrote:

> Hi,
>
> I've to setup and use SQL Server 2K db from O9i db. Basically, i've to
> periodically PULL data from SS2K into O9i tables.
>
> Have done the necessary setup as specified in Oracle docs.
>
> 1. System DSN: SQLServer2K
>
> 2. ini file: initSQLServer2K.ora
> contents of ini file
> HS_FDS_CONNECT_INFO = SQLServer2K
> HS_FDS_TRACE_LEVEL = 0
I have this set up in 8.1.7 connecting to an MsAccess "database", I assume this entry is in %oracle_home%\hs\admin?

The only difference in my init file is that I have HS_FDS_TRACE_LEVEL = ON and another line: HS_FDS_TRACE_FILE_NAME=c:\orahs1.log to enable tracing, otherwise it looks ok.

>
> 3. tnsnames entry:
> SQLServer2K =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = PC-P3654)(PORT = 1433))
> )
> (CONNECT_DATA =
> (SID = SQLServer2K)
> )
> (HS = OK)
> )

This looks ok as well
>
>
> 4. listener.ora entry
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (SID_NAME = PLSExtProc)
> (ORACLE_HOME = E:\OraHome1)
> (PROGRAM = extproc)
> )
> (SID_DESC =
> (GLOBAL_DBNAME = emfg)
> (ORACLE_HOME = E:\OraHome1)
> (SID_NAME = emfg)
> )
> (SID_DESC =
> (SID_NAME = SQLServer2K)
> (ORACLE_HOME = E:\OraHome1)
> (PROGRAM = hsodbc)
> )
> )
>
>
> Did i miss something here? Coz when trying to connect to the tables in
> SS2K, get following error:
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> select * from currency_at_sqlserver2k;
> select * from currency_at_sqlserver2k
> *
> ERROR at line 1:
> ORA-28545: error diagnosed by Net8 when connecting to an agent
> NCRO: Failed to make RSLV connection
> ORA-02063: preceding 2 lines from SQLSERVER2K
>
>
> I'm not able to figure out where the error lies.
>
> Any inputs on this??
>
> TIA
The rest looks ok from what I can see, I have in the past experienced "unexplainable" dblink failures with heterogeneous connections that just seem to start working again, so the last go around I pasted the complete address in the dblink service name field rather than the tnsnames alias and it has seemed to work ok since.

I also recall that some queries did not work for some unknown reason, and select * could have been one of them, but its a bit foggy in my memory right now. You may want to try selecting a particular field on the same table and see what the results are.

Hth Received on Wed Jul 14 2004 - 11:34:10 CDT