Easysoft ODBC-SQL Server Driver

How do I work with SQL Server VARCHAR(MAX), GUID and TEXT Columns when using Oracle Heterogeneous Services?

Article:
01068
Last Reviewed:
30th January 2024
Revision:
1

The Easysoft SQL Server ODBC driver allows Oracle to connect to SQL Server by using the generic Heterogeneous Services agent DG4ODBC.

The SQL Server ODBC driver supports all SQL Server data types. DG4ODBC supports data types that are common to most ODBC drivers, but not some of the SQL Server specific ones. If the SQL Server tables that you intend to access from Oracle contain the following data types, you will need to configure your SQL Server ODBC driver data source before connecting.

Data type Notes
VARCHAR(MAX) DG4ODBC does not support this data type. To work around this, add this line to your SQL Server ODBC driver data source:
VarMaxAsLong = Yes
GUID DG4ODBC does not support this data type. To work around this, add this line to your SQL Server ODBC driver data source:
DisguiseGuid = Yes
TEXT Although Oracle supports this type, you can only have one TEXT column per table. If you need to limit the length returned by the SQL Server ODBC driver when describing TEXT columns, add this line to your data source:
LimitLong = num

where num is the maximum length in bytes the SQL Server ODBC driver will return. This is optional and only set this if you have issues with very large TEXT columns.

Accessing SQL Server Data from Oracle

To give you some context as to where configuring the SQL Server ODBC driver data source fits into the process, the following steps show you how to connect Oracle to SQL Server via Heterogeneous Services.

  1. Create and test a SQL Server ODBC data source that connects to the target SQL Server database. For example:
    [SQLSERVER_SAMPLE] 
    Driver=Easysoft ODBC-SQL Server 
    Description=SQL Server DSN 
    Server=mymssqlmachine\myinstance 
    Logging=no
    LogFile= /tmp/sqlsrv.log
    Mars_Connection=Yes

    Please note Mars_Connection must be set to Yes, if you are connecting to SQL Server 2005 or later.

  2. Log in as the Oracle user and set your library path in your .profile or .bash_profile files. For example:
    LD_LIBRARY_PATH=/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib:$LD_LIBRARY_PATH
    export LD_LIBRARY_PATH

    Note On HP-UX, replace LD_LIBRARY_PATH with SHLIB_PATH. On AIX, replace LD_LIBRARY_PATH with LIBPATH.

    If you wish to use isql to verify the connection to SQL Server whilst logged in as the Oracle user, add the following entry as well:

    PATH=/usr/local/easysoft/unixODBC/bin:$PATH
    export PATH
  3. Either log in again as the Oracle user or run:
    ./.bash_profile
  4. In your $ORACLE_HOME/hs/admin, create an initsid.ora file. The sid name will be used throughout the connection setup. In our test case, we created initsqlsrv.ora, which has the sid name of sqlsrv.
  5. Our initsqlsrv.ora file had the following contents:
    # 
    # HS init parameters 
    # 
    HS_FDS_CONNECT_INFO = SQLSERVER_SAMPLE 
    #HS_FDS_TRACE_LEVEL = 4 
    HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so 
    HS_NLS_NCHAR=UCS2 
    #HS_FDS_SUPPORT_STATISTICS=FALSE
    # 
    # ODBC specific environment variables 
    # 
    set ODBCINI=/etc/odbc.ini
    
    Parameter Description
    HS_FDS_CONNECT_INFO This is the name of your Easysoft ODBC data source in your /etc/odbc.ini file.
    HS_FDS_TRACE_LEVEL Un-comment this line if you wish to turn on Oracle initsid.ora tracing.
    HS_FDS_SHAREABLE_NAME This is the path to the unixODBC driver manager.
    HS_NLS_NCHAR The HS_NLS_NCHAR parameter value tells DG4ODBC to pass UCS-2 encoded data to the Unicode ODBC APIs, rather than UTF-8, which is the default for some versions of DG4ODBC.
    HS_FDS_SUPPORT_STATISTICS This needs to be un-commented if you are using SQL Server 7 or 2000.

    AIX You need to extract the libodbc.so shared object from the libodbc.a file. As the user that installed the Easysoft SQL Server ODBC driver, please run:

    $ cd /usr/local/easysoft/unixODBC/lib
    $ ar -X32_64 -xv libodbc.a
    x - libodbc.so.1
    $ mv libodbc.so.1 libodbc.so
  6. Configure the Oracle listener.ora file, which is usually located in $ORACLE_HOME/network/admin. For example:
    SID_LIST_LISTENER = 
     (SID_LIST = 
       (SID_DESC= 
         (SID_NAME=sqlsrv) 
         (ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/) 
         (PROGRAM=dg4odbc) 
         (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib: 
                /usr/local/easysoft/lib) 
       ) 
     )
    Parameter Description
    SID_NAME This is the sid part of the initsid.ora file you created.
    ORACLE_HOME Your $ORACLE_HOME value.
    LD_LIBRARY_PATH The path to the libraries included in the SQL Server ODBC driver distribution. On HP-UX, replace LD_LIBRARY_PATH with SHLIB_PATH. On AIX, replace LD_LIBRARY_PATH with LIBPATH.
  7. Edit tnsnames.ora to tell Oracle which server to attach the the listener entry to. For example:
    sqlsrv_ptr= 
     (DESCRIPTION= 
       (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)) 
       (CONNECT_DATA= (SID=sqlsrv)) 
       (HS=OK) 
     ) 
    Entry Description
    sqlsrv_ptr You can call this anything allowed by Oracle. This is the name you will use when you create a database link.
    HOST The machine name of your Oracle server.
    PORT The port your Oracle server is listening on.
    SID This must be the SID name specified in your listener.ora file.
  8. Restart the Oracle listener:
    $ lsnrctl stop
    $ lsnrctl start
  9. You can now test that the Oracle configuration is correct by using tnsping:
    $ tnsping sqlsrv

    If you get an error at this point there is something wrong with your Oracle configuration. Please be aware this only tests the configuration of Oracle and not that Oracle can connect to Easysoft.

  10. Create a link from your Oracle database to the tnsnames.ora entry:
    CREATE PUBLIC DATABASE LINK "ODBC"
       CONNECT TO "test" IDENTIFIED BY VALUES 'test_pass'
       USING 'sqlsrv_ptr';

    Please note the double quote (") and 'single quote' enclosed values must be enclosed in the same way when you specify your link in Oracle.

    Value Notes
    ODBC The link name that you will use in your SQL statements.
    test The SQL Server user name.
    test_pass The SQL Server password.
    sqlsrv_ptr The name of the tnsnames.ora entry
  11. To use the link from Oracle, get the name of a SQL Server table and run:
    select * from table_name@link;
    

Problems Accessing Your Data

If you run into any issues trying to read / write data to SQL Server from Oracle please do the following:

  1. Make sure the issue is not with the application you are using, for example SQL Developer, Toad and so on. Test the issue using SQLPlus on the Oracle machine. If the issue only happens for example under Toad and not in SQLPlus, please report the issue to the people that support Toad.
  2. Try to limit the issue to the problem column / table. Let's say, for example, you are running a select * from table@link and you have only 1 column that is causing the problem, try running select column from table@link and see if that gives the same error. This helps us to diagnose the issue.
  3. Turn tracing on within your $ORACLE_HOME/hs/admin/initsid.ora file.
  4. Stop and start your Oracle listener.
  5. Reproduce the issue in SQL Plus. If you do not get an Oracle trace file in your $ORACLE_HOME/hs/log folder your listener has not been restarted or Oracle has not been configured correctly.
  6. Send the Easysoft support team ():
    1. A copy of your Oracle initsid log file. Please zip / compress this file if more than 1 MB.
    2. A copy of your initsid.ora file.
    3. A copy of the files ending in _install.info from /usr/local/easysoft.
    4. The version of SQL Server you are connecting to.
    5. The SQL used to generate the table in SQL Server you are accessing. If you are accessing a view, please send the SQL used to create the view and and tables / views that access it.

    Once we have all items, the Easysoft support team should be able to quickly work out if this is an Oracle configuration issue / bug, limitation in Oracle, Easysoft configuration / bug or if we simply need more information.

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)