Hi, Im trying to replicate some of the data in my Navision 3.60 database to a SQL server. I’m doing this by scheduling DTS packages and importing the data throuh ODBC into a SQL Server database/table. This works fine except for the fields which are of the datatype Decimal in Navision. I can’t get it to work. Always an error : status 12 and something about wrong data type conversion. I’ve even tried copying it into a field of the type variant but this couldn’t solve the problem. No error anymore, but the values aren’t copied. If someone has the answer, please let me know. I’m trying to replicate the Item table. fields No., description and “unit price”. The last one is a decimal Can you try and see if this works on your setup thanks.
You may need to check the international format. It may be that you are mixing numbers like 23.89 and 23,89.
I am on SQL Server Option and I know there are some issues with Float datatype, which Navision uses because it has decimal precision on SQL 38 decimal places. I do some importing of data from a SQL table outside of Navision into Navision tables and I found I had to take decimal data and convert it to float for Navision to accept the data. Perhaps in your DTS package you could do a CAST([unit price] as decimal(10,5)) to handle the Float data coming from Navision
Navision does not and never has used the SQL float data type natively. It has always used decimal(38,20), for its own Decimal (bcd) data type. Float is floating point and Decimal is fixed point - Navision can use the float if created externally can can map it to its bcd, but it will never create a field of that type.
Then why is it that when I import decimal data into Navision using an ADO connection, I get an Invalid Type error, yet, if I cast the very same data as Float before importing, it works?
Because float has simpler support in ADO than decimal, based on the float type having native support in C and C++. There is no native support for decimals, they must always be converted to/from strings internally which leads to problems (i.e. international issues - what should a decimal seperator be? etc). Converting to/from float/decimal causes data loss, depending on your values. Becuase there is no lossless way of going between fixed/floating point data. But Bart is using simple DTS imports anyway, not ADO. The likely culprit is what David suggests - international format.
Bart, are you going from Navision Server → SQL Server, or SQL Server → SQL Server? If SQL->SQL, check that the precision and scale of the decimal in source amd destination fields are 38,20. You can do this in Enterprise Manager of using sp_columns in Query Analyzer.