Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
"Searching" dialogue box gone after Migration to SQL
nitindabas1
Member Posts: 4
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
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
0
Comments
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.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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
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.
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool