ODBC from Access 97/200x) to SQL Navision Database: float type fields --> Text

Business Central/NAV Technical Forum
1

Hi all

I’va a problem using SQL ODBC from (Access 97/200x) to our (migrated a short time ago) SQL Navision Database.

Scenario:

-Windows 2003 Server Enterprise ENG

-SQL Server 2005 Enterprise SP3 ENG

-Navision Database: version 4.02 SP2

-Navision Server : 5.0 SP1 ITA (sql)

-Navision Client : 5. SP1 ITA

When connecting a numeric field type with an accuracy of 38.20 from a Navision table through odbc sql the value returnd of connected field is formatted into text:

Example:

Field: Standard Cost

Original Value :5,00002345

  • Result through ODBC Native : 5,00002345 (Numeric)

  • Result through ODBC SQL : 5,02 (Text)

I’m newbies on this, after searching the web results are that changing from odbc native to sql

modify many things but I cannot found what must do about the above issue for a one time solution…

… is there a way to set the driver at server side instead using CAST or similar function alltime by client side ?

I’ve read this discussion http://dynamicsuser.net/forums/p/14832/71306.aspx#71306

but not help. sorry

Any Help ?

Thanks

2

Although I have not worked on this issue before, II think your issue may be a limitation with the Jet/ODBC engine. This is an extract from (old) http://support.microsoft.com/kb/214854

ODBC SQL Type     Precision  Scale Jet 3.5 Type Jet 40 Type
SQL_DECIMAL       0 To 4     0     Integer      Decimal
SQL_DECIMAL       5 To 9     0     Long         Decimal
SQL_DECIMAL       10 to 15   0     Double       Decimal
SQL_DECIMAL       <=15       >0    Double       Decimal
SQL_DECIMAL       16 To 28   N/A   Text         Decimal
SQL_DECIMAL       > 28       N/A   Text         Text

My suggestion would be to create a SQL view of the table, and cast the decimal into a percision less than 28.

Good luck