anyone using HS Generic Connectivity?

[Timbo]

I'm running 9.2.0 on Solaris 8. Need to query SQL Server 2000 on NT.
Trying to avoid the licensing expense of transparent gateway, as only
simple queries are required.

The Generic Connectivity feature of Heterogeneous Services uses ODBC to
talk to other databsases. It requires quite a bit of setup.

Is anyone doing this? I'm attempting to use the freeTDS driver and
the iODBC driver manager.

Any experiences, tips, etc, would be appreciated.

Tim

[Timbo]

Thanks for the reply.

My primary objective is to query SQL Server on demand. I
agree it would be infinitely easier to send the data from
SQL to Oracle, but I don't have that luxury.

I already found the install notes from Oracle. I think the problem
I'm having is because we are running the 64bit version of Oracle on
the Solaris box. The odbc drivers are 32bit. Do you know if
this is a conflict?

Thanks.

Netrista Khatam wrote: Hello Tim, What's your primary objective? If you want to query SQLServer through Oracle HS--via iodbc/tds--I can provide you with basic instructions and tips to start you on your way. In fact, I'll post my modified Oracle notes at the bottom of this reply. The example uses the OpenLink ODBC Driver. Therefore, you will need to modify some sections for your own Free TDS driver. If you really want to bypass HS, there are other options. It all depends on what you want to do. If you are trying to Migrate data from SQLServer to Oracle, you could try the SQLServer DTS export feature. It is substantially easy to configure in comparison to HS. However, you will need an Oracle ODBC driver on Windows. OpenLink Software has a product that can allow you to take snapshots of SQLServer data and push them into Oracle. It's similar to the DTS Wizard, but it removes some of the headache involved with OLE DB providers and data type translation. OpenLink also has a product that can allow you to query heterogeneous data in Oracle and SQLServer simultaneously. Finally, let me know if I can help in any way. I work with a variety of database and database products, and I'm quite familiar with data migration techniques. SCOPE & Application ------------------- This example setup is from a Sun Solaris Platform running Oracle 8.1.6 to a target Microsoft SQL Server database on Windows NT using the Openlink Generic ODBC driver. 1. Install Heterogeneous Services with the Oracle Installer. If it's already installed, you'll see an "hs" directory under $ORACLE_HOME 2. Install the data dictionary tables and views for Heterogeneous Services. Log in to the Oracle database as sys and run the "caths.sql" script. This script is located in $ORACLE_HOME/rdbms/admin. The data dictionary tables and views may already be installed on the server. You can query the data dictionary to check for their existence. select table_name from dba_tables where table_name like 'HS%'; select view_name from dba_views where view_name like 'HS%'; If they are not, run the script as follows: cd $ORACLE_HOME sqlplus (provide authentication) @rdbms/admin/caths.sql 3. Install your ODBC driver and configure an ODBC DSN. Ensure that connections work by testing via the "odbctest" sample application and/or the iODBC HTTP Administrator. 4. Make sure the following entries are added to the "network/admin/tnsnames.ora" and "network/admin/listener.ora" files (change the "host" / ORACLE_HOME values according to your system): Tnsnames.ora ------------ hsodbc= (description= (address=(protocol=tcp)(host=solaris_server)(port=1521)) (connect_data=(sid=hsodbc)) (hs=ok) ) Listener.ora ------------ sid_list_listener= (sid_list= (sid_desc= (sid_name=hsodbc) (ORACLE_HOME = /dbs/oracle8i/64-bit/8.1.6) (program= hsodbc) ) ) 5. Start the Oracle listener: lsnrctl start ** You should now have a service handler for hsodbc ** 6. Make sure the following noted entries are in the inithsodbc.ora located in $ORACLE_HOME/hs/admin (sample values -- your paths may vary): # This is a sample agent init file that contains the HS parameters that are # needed for an ODBC Agent. # # HS init parameters # HS_FDS_CONNECT_INFO = **Your ODBC DSN Name** HS_FDS_TRACE_LEVEL = 4 HS_FDS_TRACE_FILE_NAME = hs.log # *** Full path to ODBC Driver Manager *** HS_FDS_SHAREABLE_NAME = /dbs/openlink/32bit/v42/lib/libiodbc.so # # ODBC specific environment variables # set ODBCINI=/dbs/openlink/32bit/v42/bin/odbc.ini set ODBCINSTINI=/dbs/openlink/32bit/v42/bin/odbcinst.ini # *** The following variables are extra requirements of the OpenLink ODBC drivers *** set PATH=$PATH:/dbs/openlink/32bit/v42/bin set LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/dbs/openlink/32bit/v42/lib 7. Connect to the Oracle database and create a database link to access the target database. Be sure to use the appropriate quotes as noted below. SQL> create database link hsodbc SQL> connect to "user" identified by "password" <= valid user/pwd on target DB SQL> using 'hsodbc'; Once that's done, you should be able to select from a remote table in your DSN: SQL> SELECT * FROM authors@hsodbc; Best regards, Netrista Khatam Technical Support Manager OpenLink Software Product Support E-Business Infrastructure Technology Provider http://www.openlinksw.com Timbo <tjbacs_nospam@attbi.com> wrote in message news:<3E011FE4.4040908@attbi.com>...I'm running 9.2.0 on Solaris 8. Need to query SQL Server 2000 on NT.Trying to avoid the licensing expense of transparent gateway, as onlysimple queries are required.The Generic Connectivity feature of Heterogeneous Services uses ODBC totalk to other databsases. It requires quite a bit of setup.Is anyone doing this? I'm attempting to use the freeTDS driver andthe iODBC driver manager.Any experiences, tips, etc, would be appreciated.Tim