This discussion is archived
4 Replies Latest reply: Jun 21, 2013 1:32 AM by mkirtley-Oracle RSS

ODBC connectivity to SQL-Server fails

BillyVerreynne Oracle ACE
Currently Being Moderated

Oracle 11.2.0.2 on Enterprise Linux Enterprise Linux Server release 5.4 (Carthage).

 

Using FreeTDS unixODBC Driver to connect to a MS SQL-Server 2008 R2 SP1database. (RPM package freetds-0.64-1.el5.rf.x86_64 reported by yum whatprovides for ODBC FreeTDS .so).

 

ODBC isql connectivity works and SQL select Col1 from Schema.Table is successfully executed against DSN, and returns rows/data (not real names).

 

Same SQL in Oracle, select "Col1" from "Schema"."Table"@dblink fails (using the same ODBC DSN as isql test cor connectivity), via sqlplus.

 

Debug trace (HS_FDS_TRACE_LEVEL=debug) reports:

Exiting hgopars, rc=28500 at 2013/06/19-14:12:08 with error ptr FILE:hgopars.c LINE:570 FUNCTION:hgopars() ID:SELECT list of size 0 is not valid

Have enabled/disabled numerous combinations of odbc.ini and init.ora parameters such QuotedIdHS_FDS_SQLLEN_INTERPRETATION, HS_FDS_SUPPORT_STATISTICS and so on. Also tried using DBMS_HS_PASSTHROUGH, instead of native SQL via sqlplus. Same error, consistently.

 

Interestingly, the desc "Schema"."Table"@dblink call works fine in sqlplus - but according to the debug trace it lets the ODBC driver deal with it - it does not craft a SQL and pass this to the ODBC driver, according to the debug trace. Instead an ODBC interface call is used to describe the object.

 

So, ODBC driver seems  perfectly fine. Oracle can successfully use it as the desc command proved. Except that SQL statements (from Oracle) via the driver fails.

 

Any ideas as to what the problem can be?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points