Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-02
3
Medium Priority
?
1,218 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 43

Accepted Solution

by:
Eugene Z earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

926 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