Solved

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

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
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.
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.

816 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

11 Experts available now in Live!

Get 1:1 Help Now