NAV 4.00 SP2 AND SQL2005?

Business Central/NAV Technical Forum
1

Hello,

who has already experience with Navision 4.00 SP2 on a SQL2005 database. Is it stable, know issues? Thanks.

2

We have recently upgraded an installation from 3.70.B & SQL 2000 to 4.00 SP2 Updt 1 & SQL 2005 (technical upgrade, the application is still 3.70 objects).

Well, the upgrade was more painful than expected: There have been a lot of issues with user rights & logins (automatic re-creation of several hundred logins, problems with linked tables & views), some issues still remain: even though everything was synchronized succesful, we received once in a while some permission errors, so users could not access specific tables, where it actually was allowed by NAV user rights settings … the only way to bypass this, was to assign all users to the sysadmin role.

Another issue is this - example:

IF GLEntry.FIND(’+’) THEN
NewNo := GLEntry.“Entry No.” + 1;
GLEntry.INIT;
GLEntry.“Entry No.” := NewNo;
GLEntry.INSERT;
CodeunitX.functionY(); // just leave the current object
GLEntry.FIND(’+’)

Let’s assume in the beginning the last found “Entry No.” was 10, hence Last No would be set to 11 and the newGLEntry No. 11 would be inserted. Now it comes:

If you are sysadmin, the last line GLEntry.FIND(’+’) returns record 11 - which I consider OK -, if you’re not sysadmin the record no. 10 is returned!!! And that’s a severe problem which could really f*** up the whole application!

This is already addressed to Microsoft, and indeed that’s a real bug which will be solved in a Hotfix which should be released soon.

Overall, the system NAV 4.00 & SQL 2005 workes quite OK. There are some minor issues which should be solved with SQL Server 2005 SP1, we are currently testing the Update patch.

Another thing is, that SQL2005 creates different execution plans for some requests. In 2000 we have done a lot of optimizations of indexes & stuff, some of those did not have a positive effect in 2005, so further tuning is required. But after changing some indexes, well, it works …

After all, we are still in progress of optimizing the system and getting our experiences with it - and of course I’ll share that experiences here! To be continued …

3

Hi Jorg,

Have you tried adding all the tables to a Schema, and then giving all the principles that Schema as their default?

As default in SQL 2005 they will have the DBO Schema unless you specuify otherwise.

You could add all Logins (Principles) to a new schema such as “Navision” which has all Navision Related tables in it. Then if they are trying to do something more complicated SQL05 will default back to the DBO schema to check for system tables, and if they don’t have that permission it will fail.

Just something to try, not actually done it myself… yet.

That might not resolve the Execution Plan issue, but “should” resolve assigning everyone sysadmin.

Regards

T

4

I am going to be converting from SQL 2000 to SQL2005 in the next 90 days. Following in much the same steps as “stryk” has.

Any more tips and experiences from forum memebers will be very usefull [:D]. I am a little concerned with potentially having to set all users as sysadmin and issues as stryk described.

Thanks

5

Since recently there is a hotfix for 4.0SP2 for SQL2005.
This is the link: http://mbs.microsoft.com/public/insights/ReadArticle.aspx?rcpt_id=12842408&ja_id=37644

6

That is build 22979. I am currently working with a client on a technical upgrade of a large 3.60 database. I have been given a pre-release of the SP3 client (build 23099) to address sonme performance issues. I am not sure when this build will be released.

7

I am running 3.70A database with 4.02.23099 client - SQL 2000. All seems ok.

8

Have you tried adding all the tables to a Schema, and then giving all the principles that Schema as their default?

Hi Tony, (sorry for the late reply, have been on vacation [ip])

No haven’t done this because we want to use the standard NAV features for Application Roles/Schemas and Logins …

Regards,

Jörg