Sort order after changing to SQL database

drecksgekritzel

Hello,
we are planning a change from cside to sql database.
And now we have problems with the sort order during our tests.
We still get an error message when we want to change the SQL Data Type value into Variant.
we did it like this way:
open the table "27 Item" in the object designer in design mode. Then properties of the first field "No.". And then we set "SQL Data Type" to "Variant". During the save and compile process the error message appears.
The message is in german.
I try to translate it:
the conversion of the varchar value 5012800001 leads to an int-column overflow.
the highest value of a whole number is exceeded.

And we have a second error too.
if we do the same procedure in the table "5050 Contact" a other appears.
i try to translate it too:
the index for the contact table with the name '$1' already exists.


i hope that there is a way to change the sort order without this errors and someone can show it to me...

Thanks.

Kind Regards

Alex

kine

Problem is that "5012800001" is too big for integer. (integer max is 2147483647). You will need to add some character as prefix to take it as a code, not integer. Numbering convention is to have something like "CNNNNNNNNN" - number code beginning with one or more letters with fixed length of the code. In this case you do not need to change the SQL Datatype.

Waldo

If renumbering is no option, you could always try to go to Variant as SQL Datatype.

kine

Waldo wrote:

If renumbering is no option, you could always try to go to Variant as SQL Datatype.

But this is the problem- you cannot change the datatype because the error... :-) NAV is checking if there are Integer values and during that there is overflow...

Waldo

also with Variant?? :-k
I know you get if with "Integer", but Variant as well?

kine

drecksgekritzel wrote that yes... 8)

Waldo

So far for my testapp :oops:

drecksgekritzel

ok, now I am back in office and i have solved the problem with the integer overflow by changing the relevant values.
thanks for your help!
but can you please help me with the second error message?
(the index for the contact table with the name '$1' already exists.)

Kind Regards

Alex

DenSter

First, save the table, by exporting the objet as an fob file. Then, disable all keys, except for the primary key, and save the table. At this time, the only index on SQL Server should be the one called "$0". Then import the fob back in, which should then create all secondary indexes on SQL Server.

drecksgekritzel

it happens exactly that what you have described but the error is still here...

DenSter

next would be to check the SQL Server table design, and see if it can be fixed directly. I'd suspect that someone created an index on SQL Server using NAV naming conventions, and the only way to fix that is to remove those indexes manually. Don't forget that every table definition in NAV results in a physical table for every company in the database, so you'll have to do that for each company.