Hello, I have problems with retrieving some datatypes from the Navision database in SQL Server 2000. I use Linked Server with Navision ODBC dirver(C/ODBC 2.60.02.5709) When retrieving datatype ‘Code’ or ‘Decimal’ I get the following errormessage: OLE DB provider ‘MSDASQL’ returned an unexpected data length for the fixed-length column ‘[MSDASQL].Nr_’. The expected data length is 21, while the returned data length is 0. OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName=‘MSDASQL’, TableName=’[MSDASQL]’, ColumnName=‘Nr_’, ExpectedLength=‘21’, ReturnedLength=‘0’]. The query I use: select Top 100* from OPENQuery(NAVISION,‘select Nr_ from klant’) When I ask for Naam and not Nr_ then it works properly Regards, Hans de Korte - hans.de.korte@prominent.nl ProMinent Verder Holland
This is because the ODBC driver is incorrectly exposing Code and Decimal as a fixed-length CHAR data type instead of a VARCHAR. With a CHAR, every value has the same length, which is the maximum defined length of the field. With a VARCHAR, the lengths of each value can be different. C/ODBC is telling the OLEDB provider for ODBC, that this code field is CHAR and therefore every value will have the same length; when OLEDB finds a value of a different (smaller) length it complains. I’m afraid theres nothing to be done about it unless C/ODBC is fixed. This error is still present in the latest driver version.
Hello Robert, Thanks for your answer![:)] Is it possible in Navision to make ‘views’ of tables where the datatypes code and decimal can be converted to datatype the ODBC can handle correctly. Then retrieve the data from that view into SQL by making use of the ODBC? Regards, Hans
No. Only tables, and the problem is with the meta-data for the tables.