Solved

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

Posted on 2009-04-02
3
1,142 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
  • 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 42

Accepted Solution

by:
EugeneZ 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now