Hi all,
after spending the sunniest days of the year searching for a solution I finally surrender and hope for your help:
I need to select data from a SQL Server 2012 running on Windows 2008 64bit (Collation Latin1_General_CI_AS) data to a Oracle XE 11g.2 database 32bit on an Windows 2008 64bit server.
Oracle Settings:
NLS_LANGUAGE GERMAN
NLS_TERRITORY AUSTRIA
NLS_CURRENCY €
NLS_ISO_CURRENCY AUSTRIA
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RRRR
NLS_DATE_LANGUAGE GERMAN
NLS_CHARACTERSET AL32UTF8
NLS_SORT GERMAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
--> installed the standard ODBC32bit driver for SQL Server (working), Access to SQL Server via SQLDeceloper, Excel etc 100% working
--> made all necessary changes to listener.ora and tnsnames.ora, in my opinion also working 100% because connection is working
--> created initSRVBMD.ora for the heterougenous service (the cause of all my problems I suppose):
# HS init parameters
#
HS_FDS_CONNECT_INFO = SRVBMD
HS_FDS_TRACE_LEVEL = Debug
HS_FDS_TRACE_FILE_NAME = SRVBMD.LOG
# also tried tons of settings which seem to have no effect at all after lsnrctl reload
# HS_LANGUAGE=AL32UTF8
# HS_FDS_REMOTE_DB_CHARSET=WE8ISO8859P1
# HS_FDS_REPORT_REAL_AS_DOUBLE = TRUE
#HS_LANGUAGE = ENGLISH_AMERICA.WE8ISO8859P1
#HS_NLS_NCHAR = UCS2
--> created database link working
--> select * from table@server shows a lot of columns, but I can just see SQL Server datatypes datetime/int/numeric but no biginteger, no smallint, no nvarchar.
When directly selecting a nvarchar column I get ORA-00904: "fieldname": invalid identifier 00904. 00000 - "%s: invalid identifier"
I use dg4odbc (included in Oracle XE) and didn't change anything here. I have a really straight-forward setting (standard Oracle XE installation, standard SQL Server ODBC driver)
Since ODDC is working and dg4odbc delivers some data I set "HS_FDS_TRACE_LEVEL = Debug" in initSRVBMD.ora to see whats happening there but no logfiles are generated at all. I'm APEX developer and not really used to ORA administration so perhaps made some general mistakes?
All I need is no select a couple fields from SQLServer, especially nvarchar (performance is not critical, I'll make a materializes view afterwards).
A fancy SELECT CAST would do the job or (preferable) some way to make my heterourgenous gateway work for all data types.
Thanks a lot for your time!
Juergen