Solved

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

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

821 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