Using C/ODBC with OLE DB (SQL Server) and fixed length fields

Hello, I have a serious problem with the C/ODBC driver. I try to exchange information between the native Navision database and Microsoft SQL Server 7 using the OLEDB for ODBC driver. First I created a system DSN with C/ODBC pointing to the Navision Financials 2.5 database. Then I created a linked server in SQL Server 7 using the OLEDB for ODBC driver pointing to the just created DSN. After completing these steps it is possible to query the Navision database with the following query: (Klant is dutch for Customer) select * from navtest…Klant Or querying the database without a linked server: select * from openrowset(‘MSDASQL.1’,‘NAVTEST’;‘super’;’’, Klant) or using the new MSDASQL (OLEDB for ODBC) library: select * from openrowset(‘MSDASQL’,‘NAVTEST’;‘super’;’’, Klant) All three variants result in the following error: Server: Msg 7347, Level 16, State 1, Line 1 OLE DB provider ‘MSDASQL’ returned an unexpected data length for the fixed-length column ‘[navtest]…[Klant].Nr_’. The expected data length is 21, while the returned data length is 5. This is caused by the fields of type CODE. Using a SELECT clause with other field types works fine. Because of the fixed-length nature of the CODE field, OLEDB reports an error when the returned length is shorter than the fixed-length definition. Navision v2.01 exibits the same problem. And the strange thing is that VB code using the same OLEDB driver doesn’t give any problems with the CODE fields. I tried to look for fixes on the Microsoft site and I search on deja.com and remarq.com and didn’t find any solution. I think there are 2 possible solutions for this problem, modifying the CODBC driver: - reporting the CODE fields as varchar instead of char - reporting the value of the code fields with trailing spaces up to the max-length of the field I have sent this email also to Navision support, hoping for a solution or a hotfix. I want to share this problem here, hoping someone else can help me. My configuration is: Microsoft Data Access Components 2.5 CODBC 2.50.0.1869 Microsoft SQL Server 7.0 SP2

Hi, I´ve encountered the exact same problem when trying to create views in SQL 7.0 to the native navision database throug the c/odbc driver. Please let med now if you find any solution. Best regards, Andreas Åsander, EnterNet

The ODBC driver for NF 2.5 is known for having bugs. You must upgrade to NF 2.6 to make connections successfully. John

The problem described above remains in 2.60C

Could you please provide your ODBC setting for NF DSN? Dodo

Please note that if you are trying to query from SQL Server then you need to use the Micrsoft SQL driver not the Navision C/ODBC driver. Navision’s C/ODBC driver doesn’t support OLE DB. Michael

quote:


Please note that if you are trying to query from SQL Server then you need to use the Micrsoft SQL driver not the Navision C/ODBC driver.


The problem here (for all i guess) is to declare views in SQL server that reads a native C/SIDE database trough C/ODBC