Restoring a Navision backup from SQL Server to Nav DB

KAdamsInCo

I have been told by our ISV that I should be able to do a backup using the Navision client tools from a SQL Server 2005 database and restore it to a Navision native database without any technical issues whatsoever.

The restore appeared to work successfully, but when I go into a card to view records a dialogue box appears that says 'Searching xxxx Table'. It does a sequential scan through the entire table, and the card eventually does display, but closing off the card and reopening it creates the same situation. An example of the message I receive is:

Searching the 'Constituent Ledger Entry' table.
Table: Constituent Ledger Entry
Key: Entry No.

Any record movement (forward, backward, F5 for the list) causes the search to start again. Basically, the application is not useable like this.

I went into the table and modified the primary key, let it rebuild all indexes, put the PK back the way it was, and let the indexes rebuild again. This does not appear to make a difference.

What can be done, if anything, to make a database that came from SQL Server work properly again in a native Navision DB container? We are running Dynamics-NAV 4.02 SP2 with SQL Server 2005.

Thanks,

Keaton

ara3n

Hello
It looks like, it could be two issues. One there is a filter on the card. By removing the filter, it should solve the issue.

The second one, which I suspect is the real cause is bad programming practice. The developer did not put the setcurrentkey function before setting filter. This works find on sql, because sql decides the best key, but this is not the cause on Native. The Native server is very dumb.


So turn on the debugger before opening the form and then open the form. run through the code till it reach a point where it takes a long time to step through it and you'll some code similar to this.

record.setrange(field1,somevalue);
record.setrange(field2,somevalue);
if record.find('-') then repeat

until record.next = 0;


What you need to do is put one line of code above the setrange.

record.setcurrentkey( a key that has field1 and field2 in it);

KAdamsInCo

Well, the code does run fine against other Navision native databases. If I use the ISV's demo database, which uses the same code set as what we are running against SQL server we do not have this problem.

It appears that something happens to the data/indexes when you do a backup from SQL and restore it to Nav native.

ara3n

The reason it runs fine in ISV demo database is because you don't have that many records in Constituent Ledger Entry table. insert in that table 10000 records and you'll see the same problem.

David_Singleton

KAdamsInCo wrote:

Well, the code does run fine against other Navision native databases. If I use the ISV's demo database, which uses the same code set as what we are running against SQL server we do not have this problem.

It appears that something happens to the data/indexes when you do a backup from SQL and restore it to Nav native.

There really is no such thing as "Rebuilding keys" in Native DB. A key could have been deactivated, but then you would get an error message when you try to open the form.

I would say 99% Ahmed is correct, that its Bad programming.

To confirm that he is correct, do this:


1/ Check filters on the actual Card
a)Open the form (be patient and let it complete painting the whole form and data).
b) Press Ctrl F7 and see what filters are set.
c) Go back to the form and check the key that is being used.

If the Key does not contain all the fields that are filtered, then thats the first place to look (because its the easiest to fix).

2/ If that is not the issue, then the next thing to do (on the same Card), is to press F6 on every flow field, and then repeat the above steps (1) in the form that opens.

3/ If that is not the issue, then probably there is some code that is causing the problem.

UPDATE,

just as I was about to post, I read:

Searching the 'Constituent Ledger Entry' table.
Table: Constituent Ledger Entry
Key: Entry No.

So instead of 99% I think its 99.99% that Ahmed is correct, in which case its the third option on my list.

PS after all the writing, I am not going to delete it, since it may beuse to someone else later.

Marije_Brummel

The thing is that if you forget to put SETCURRENTKEY in a SQL database, the performance usualy is fine because of the way SQL creates execution plans.

If you restore a backup on a 'dumb' native db, you need those statements.

That's why one codebase is difficult; doable but difficult.

Good luck.