Solved

Accessing SQL Server 2005 using HSODBC with 10g oracle on winnt

Posted on 2009-04-02
3
1,170 Views
Last Modified: 2013-12-18
I need read access from an Oracle 10.2.0.4 database (Win 2003 Server) to a MS SQL Server 2005 database using collation: SQL_Latin1_General_CP1_CI_AS.  The tables have nvarchar and ntext columns.

The oracle database has the following nsl_database_parameters set:
NLS_NCHAR_CHARACTERSET     AL16UTF16
NLS_CHARACTERSET                   AL32UTF8
NLS_NCHAR_CONV_EXCP            FALSE

I've installed the mssql SQL Native Client and set up a System DSN in the ODBC Data Source Administrator.

Since I only need read access, I tried using Oracle heterogeneous services (hsodbc) to connect to mssql.  I created an init<odbcname>.ora file with the following HS init parameters:
    HS_FDS_CONNECT_INFO = ovsqlserver
    HS_FDS_TRACE_LEVEL = OFF
    HS_NLS_NCHAR = UTF8

I added the info needed to listener.ora and tnsnames.ora and established a successful connection to the mssql data.  Finally, created the needed database link in the Oracle database.

I can query tables on mssql BUT any column defined as nvarchar or ntext do not show up.  Oracle clearly states that if there are datatypes it can't translate, it won't include the column.

Now I find that HSODBC has been desupported as of 15Mar09 and that one should use DG4ODBC included with the 11g release.  Arrgggg!!!

I installed only the 11.1.0.7 Gateway for ODBC to get the DB4ODBC.  Other forum searches state that DB4ODBC only works with an 11.1 listener but I don't have a full install of 11.1 with the requisite libraries.

So.  Is there a way to see the nvarchar columns in my admittedly desupported HSODBC configuration?  Is it a char set mismatch issue?  Are there other tweaks that have to made to get the ODBC driver or HSODBC to recognize the nvarchar and ntext columns?

I've been working on various configurations for a week and still no joy.  Surely this has been done many times before.   Your help would be deeply appreciated.
0
Comment
Question by:bkono
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:bkono
ID: 24047478
Further searching on this site found one answer is to use oracle's hsoledb rather than hsodbc.  I have no experience with ole db.  Will this solve the nvarchar issue?  Can anyone recommend this as a solution?   If so, can you provide the appropriate value for the init.ora file for the HS_FDS_CONNECT_INFO="UDLFILE=<full path name of the udl file>" parameter?  How do I determine the correct UDLFILE?
Thank you.
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 24056381
0
 

Author Closing Comment

by:bkono
ID: 31565675
Using the 2nd and 3rd links, I created the udl file and the oledb connection from the Oracle box.  I am able to see all the data columns now.  Thanks so much.  
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question