Question Status

Suggested Answer
saurabhabh asked a question on 18 Jun 2013 5:41 AM

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 ..

Reply
Suggested Answer
Aleksandar Totovic responded on 18 Jun 2013 11:28 PM

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.

Reply
saurabhabh responded on 18 Jun 2013 9:07 PM

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 ?

Reply
saurabhabh responded on 18 Jun 2013 10:32 PM

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.

Reply
Suggested Answer
Aleksandar Totovic responded on 18 Jun 2013 11:28 PM

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.

Reply
saurabhabh responded on 18 Jun 2013 11:42 PM

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.

Reply
saurabhabh responded on 19 Jun 2013 12:07 AM

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.  

Reply
saurabhabh responded on 19 Jun 2013 4:47 AM

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

Reply
saurabhabh responded on 20 Jun 2013 1:55 AM

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.

Reply