"Searching" dialogue box gone after Migration to SQL

nitindabas1

Hi,

I just migrated my client's database from Navision 5.01 Native to Navision 5.01 SQL. We are using SQL 2005 server. With binary code page 437 collation.

Whenever we try to apply filter that is not in sync with the key set on the form, system shows "Not Responding" for some time and then it shows the data.

Actually in such case it should open a dialogue box saying "Searchin in <table name> table" and display the search record counter. But in our case it is just searching without this dialogue box and Navision window becomes not responding for a brief period of time, this force the user to think that the Navision is hanged and user closes the navision.

What can be possible reason for this and how to solve this...

Thanks,
Nitin Dabas

Freelancer
Techno-Functional Consultant
Dubai, UAE

kriki

Don't worry, this is all normal.

The NAV client has some continuous contact with the native DB server and can do that because the native DB server searches record-per-record and thus can send an update-status to the client that can show it.
The SQL DB works set-based and for this cannot send a status update to the client. In this way the client cannot do otherwise than wait for SQL to respond. and this results that Windows says the NAV window is not responsive.

You need to solve this by 'installing' some patience into your users.

It is also possible that the table does not have the correct indexes for a query. But I think more that the users are doing some 'unusual' filtering on tables.

Other ways to improve could be to get a specialist to check (and maybe fix) your installation to see if the hardware is up to its task, the setup of the disks/hardware/Windows/SQL/NAV is correct to get the best performance out of a SQL DB.

nitindabas1

Thanks for your reply.. Let me see if they can understand this..
Atleast I can show this post to them so make them believe......


Another question I would like to ask is about the collation..
The current sorting of the item nos is as follows
1
11
111
2
21
22
234
....

What they want is same as Native DB sorting.... i.e....
1
2
11
21
22
111
234
....

For this what collation should I use in SQL Server......

Thanks,
Nitin Dabas

stryk

Hi!

The datatype "Code" actually was/is an alpha-numeric type, so basically "Code" fields should always have been sorted alphabetically (1,10,2,30,...). The native Server did not care about this conventions and provided numeric sorting (1,2,3,...), actually promoted as a feature.

No such thing with SQL Server: aplha-numeric types are sorted alphabetically.
"Code" is "varchar" in SQL Server by default, hence the technically correct alphabetic sorting. But with NAV it is possible to change this:
"Code fields have a property "SQLDataType". E.g. if you set this to "Integer" or "Variant" you will be back on the old numeric sorting.
But there are several things to regard:
1) You might need to change the "SQLDataType" of any fields which relate to the "Code" field
2) With "Integer" and "Variant" no leading zeros are allowed, e.g. with "Code" 01, 001 and 0001 are different values; all this would become 1, thus you might encounter Primary Key violations.
3) With "Variant" SQL Server needs to internally convert the field-values, which couold decrease performance.

Thus, changing an existing PK "Code" field could be quite an issue - you need to test this thoroughly.
An alternative could be to create a second numeric field and use this for sorting instead of the original "Code" field.

nitindabas1

Thanks a lot for your answer, this was really helpful..