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:
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 220.127.116.11 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.