Re: anyone using HS Generic Connectivity?

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)
> _at_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_at_hsodbc;
>
>
> Best regards,
> Netrista Khatam
> Technical Support Manager
> OpenLink Software Product Support
> E-Business Infrastructure Technology Provider
> http://www.openlinksw.com
>
>
> Timbo <tjbacs_nospam_at_attbi.com> wrote in message news:<3E011FE4.4040908_at_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 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