Oracle Database Discussions

Hi,

We’re experiencing an unusual problem with queries executed across a heterogeneous link. We are using unixODBC v2.2.12 and freetds v0.64 to build a heterogeneous link from an Oracle 11g database to an Microsoft SQL Server database. Everything, apart from the MS SQL Server, database is running on Oracle Enterprise Linux Enterprise Linux Server release 5.5 (Carthage.)

At first glance the link appears to be working, and queries return the expected results – see the following examples:

SQL> select count(*) from area@sqlsvrdb;

COUNT(*)
~~~~~
3

and

SQL> select "ID" from area@sqlsvrdb where "Area"='Zone1';

ID
~~~~
16384

both work successfully. So far so good!

Also, when I run a query to retrieve multiple VARCHAR fields from the MS SQL database, this works mostly without a problem, viz.

SQL> select "Area" from area@sqlsvrdb;

Area
~~~~
Zone1
Zone2
Zone3

Though I suspect that some of the longer strings are being truncated.

However when I run a query to select multiple numeric values from the MS SQL database, it fails, viz.


SQL> select "ID" from area@sqlsvrdb;
ERROR:
ORA-28528: Heterogeneous Services datatype conversion error
ORA-02063: preceding line from SUPPORTAL


no rows selected

Can anyone please offer any suggestions as to why this is happening?

The area table on the MS SQL Server is defined as follows:

SQL> desc area@sqlsvrdb
Name Null? Type
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Area NOT NULL VARCHAR2(360)
ID NOT NULL NUMBER(5)
Descr VARCHAR2(600)
Show NOT NULL NUMBER(3)
Xval NUMBER(3)

Note that unixODBC has been built with “CFLAGS=-DBUILD_REAL_64_BIT_MODE”. Also, we’ve set HS_FDS_SQLLEN_INTERPRETATION=64 in the link’s init file.

All of these queries have worked successfully with a 32-bit build of the same versions of unixODBC and FreeTDS as given above. I suspect that the problem, at least in part, lies in the transition from hsodbc to dg4odbc. Is this correct? Is there any way to confirm this?

Many thanks!