Data Type mismatch Nav 2013 issue

This question has suggested answer(s)

Hi guys ,

Recently i upgraded Nav2009 R2 to nav 2013 and when i try to access my pages which in turn access the tables it gives me error saying that the value contained in the SQL server is incompatible with the data type of the table created in Navision. But this was working perfectly fine in Nav 2009 R2.

what is wierd is that it is showing that the small int and the integer datatypes as mismatch. Not able to understand why it should happen.

Please see the attached file for the complete error.

Hoping for a quick reply ..

All Replies
  • Are you insert or modify some field directly through SQL?

  • Hi Aleksandar,

    This it is the error message that I am getting :

    " Table content in ProductionBatch table on SQL Server contains a value that is not compatible with the corresponding data type that is defined for the table.

    Feild : SiteID

    Type: Integer

    SQLType: tinyint

    The Table Design may have been changed outside Microsoft Dynamics Nav.

    Page must close. "

    Can you help ?

  • In some post i read that Nav 2013 is more compatible with the SQL server 2013 . So i tried that as well Still its giving the Same error. If i change the SQL Data type from 'tinyint' to 'integer' it works fine. But i dont want to make this change as it would cause me a lot of databse redesigning. Also according to microsoft (msdn.microsoft.com/.../dd338749(v=nav.70).aspx ) tinyint is compatible with integer data type of Nav. I am unable to understand why it is giving compatibility issue.

  • Ar you create this field directly in SQL? If answer is YES, this is probably reason of your problem. They fields are compatibile, but I suggest you always create new field (and table) in NAV desginer and NAV will make appropriate Data Type in SQL.

  • Thanks a lot for the quick reply,

    But the situation is that we have already a running database which we access by making the link tables in Nav. The database has its field as 'tinyint' we create a view in Nav db with the same data type. Then we create a link table which accesses this view in Nav.

  • We have 2 databases 1] Navdatabase  2]userdatabase

    I would like to mention that the link table accesses data from 'userdatabse' and not from its own 'Navdatabse'.

    This is achieved by creating a  view in 'Navdatabase' whose source is 'Userdatabse'. This View is then linked to a table in Nav. So now we are able to access the data from the 'userdatabase' in Nav.  

  • Hope you found solution

    mibuso.com/.../viewtopic.php

  • Hi mohana..

    I don't know if that's the preferred solution . I mean why should it not work in Nav 2013 ? while it was working perfectly fine in Nav 2009 R2. Also this would mean that I need to cast all my views that I use for the linked tables. Does that mean that Nav 2013 does not support SQL Data Types Bit, TinyInt, Small int any more??

    Is there any official statement that would say so . ?

    Hoping you will help me out ..

    Thanks and regards,

    Saurabh

  • HI there,

    As suggested by the post I did try to cast them. But then I am not able to insert , update using the views as it become a derived field.