Error: ORA-28500:[Oracle][ODBC SQL Server Driver]String data, right truncation

Question

We are trying to connect to Sql Server 2005 via Oracle 11gR2 gateway and when we query a view that gets data from Sql Server , we get this error :

Error: ORA-28500:[Oracle][ODBC SQL Server Driver]String data, right truncation 

I have commented out some columns in the query running at Oracle, and it worked. The commented columns are in data type of VARHCAR(30), but there are other fields which are VARCHAR(30) too.

Is there any workaround for this error, if I add these 3 columns that fail the whole query, everything will be O.K., I tried to rename these fields at the view running at Sql Server 2005 which is the data source, no result.

Both ODBC and SQL Server drivers are installed, we are using the SQL Server driver,"dg4msql". Oracle charset is AL32UTF8.

Answer 1

The most likely cause of this is that these column contain characters that Oracle will use more than one byte to store. The gateways sees that the column is defined in SQL Server as VARCHAR(30) and so it makes a VARCHAR2(30) column in Oracle, however (30) here is 30 bytes not 30 characters, so you can get truncation of the data if the total number of bytes Oracle wants to use is more than 30. (i.e. You have 30 characters in SQL Server but one of then requires 2 bytes in Oracle and so the length is now 31 so therefore too big).

As a workaround, change the VARCHAR(30) columns in the SQL Server to NVARCHAR(30) - the gateway will then know that it might get mutli-byte characters and translate the column definition as NVARCHAR and allow the inserts.

See the Gateway Data Type Conversion Table for 11gR2 for more information on how the columns will eb translated.