Incompatibilty SQL Server 2005 with Navision 37B

nusyaputera

Dear World,

I tried to transfer Navision 37b database from SQL 2000 to SQL 2005.

All user account on SQL 2005 has been created (with public serverroles,
as like as on SQL 2000).

After Restore & Syncronize from Navision 37B Client, I can only login with 'sa' account,
all other user account login failed with message (as on the attached picture):
==================================================
The following SQL Server error(s) occured:

4064,"42000"",[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open user default database. Login Failed.
==================================================


But when I tried giving them a 'sysadmin' serverroles, it works.

Is there any way so, all user account has 'public' roles, but able to login normally?
(User 'roles' at Navision client aren't changes at all)

Thank you.

kriki

SQL2005 is not guaranteed to work with 3.7B.
So best is to (technically) upgrade to 4.0SP3 with latest hotfix.

After that, all should work well.

nusyaputera

Hi Kriki, thanks for your reply,
We have so many customization on our Navision.
and I see so many forums in mibuso talking about upgrading-problems.

Now I'm still focus on Navision Upgrade Toolkit
(learning & testing--perhaps 'upgrade' is the best solution... like you said).

But I'm still looking another solution for this,
so (pardon Mr.Admin/Mr Luc) I will kept my post still unsolved for about 2 week ahead.

Thank you.

kine

nusyaputera wrote:

Hi Kriki, thanks for your reply,
We have so many customization on our Navision.
and I see so many forums in mibuso talking about upgrading-problems.

Now I'm still focus on Navision Upgrade Toolkit
(learning & testing--perhaps 'upgrade' is the best solution... like you said).

But I'm still looking another solution for this,
so (pardon Mr.Admin/Mr Luc) I will kept my post still unsolved for about 2 week ahead.

Thank you.

We are talking about "Technical upgrade" not upgrade. Technical upgrade means just using new .exe files with same objects, without any change to the objects in your DB. You do not need to upgrade the application itself (the NAV objects) to be able to use newer client version. Just look for more info about Technical Upgrade and you will see what we mean.

bbrown

First, I agree that you should at least do a technical upgrade.

Did you change the database name when you move it? You get this error because the user is pointing to a default database that either does not exist or they don't have permissions for. Reset each user's default database setting to MASTER. This is the default and there's no reason to change it on a NAV system.

If they are already set to MASTER, verify that the users are members of the SQL Server role PUBLIC.

David_Singleton

nusyaputera wrote:

Hi Kriki, thanks for your reply,
We have so many customization on our Navision.
and I see so many forums in mibuso talking about upgrading-problems.

Now I'm still focus on Navision Upgrade Toolkit
(learning & testing--perhaps 'upgrade' is the best solution... like you said).

But I'm still looking another solution for this,
so (pardon Mr.Admin/Mr Luc) I will kept my post still unsolved for about 2 week ahead.

Thank you.

here is an explanation of the steps needed t do a Technical Upgrade

NAV technical upgrade

nusyaputera

Hi kine,
Thank you so much for telling me about "technical upgrade".
In my opinion, technical upgrade means:
I will have to make "a-new-version-of-navision-database" from "New-Navision-Client-Version", and restore my "previous-version-of-navision-database" into it.

If that so, I have to make sure all the database object (that created by new .exe navision client) have 100% same with the previous version. I will do some test like the instruction given at NAV technical upgrade (Thanks to David Singleton).

bbrown wrote:

First, I agree that you should at least do a technical upgrade.

Did you change the database name when you move it? You get this error because the user is pointing to a default database that either does not exist or they don't have permissions for. Reset each user's default database setting to MASTER. This is the default and there's no reason to change it on a NAV system.

If they are already set to MASTER, verify that the users are members of the SQL Server role PUBLIC.

Hi bbrown,
I'm not change the database name, and the user has been pointed to the correct database (NAV Database)--with "SQL public roles" . Thay can login Normally-Only-if they have "sysadmin roles". Thank you.

kine

nusyaputera wrote:

Hi kine,
Thank you so much for telling me about "technical upgrade".
In my opinion, technical upgrade means:
I will have to make "a-new-version-of-navision-database" from "New-Navision-Client-Version", and restore my "previous-version-of-navision-database" into it.

If that so, I have to make sure all the database object (that created by new .exe navision client) have 100% same with the previous version. I will do some test like the instruction given at NAV technical upgrade (Thanks to David Singleton).

There are two ways:
1) Just open the database with newer client and confirm the upgrade dialog. Client will change technical things on the database (some background structures), and you are done.From this point, you can open the database only with the newer version
2) Backup-Restore way - but because objects are part of the backup-restore procedure, all objects which were in previous version, will be same in new version. There will be some new objects, but these you cannot compare with previous version because the are new... 8) Of course, testing is good thing in all cases...

rdebath

kriki wrote:

So best is to (technically) upgrade to 4.0SP3 with latest hotfix.

After that, all should work well.

BEWARE :bug: :bug: :bug:

Dataports now (V4sp3 update6 and V5+) clear the import record between lines. This can (AND DID at one or our customers) delete data in your database.

As far as I know Microsoft haven't introduced any other potentially destructive changes. (Though, V5 temporary table form will, unfortunately, run in a V4 database with potentially "unhappy" results. So be even more careful about backports!)

BTW: For history buffs Navision did something about as bad when they started validating dataport columns by default in 3.10, they reverted the change, but it seems to be against Microsoft's religion to revert any change.

kriki

rdebath wrote:

kriki wrote:

So best is to (technically) upgrade to 4.0SP3 with latest hotfix.

After that, all should work well.

BEWARE :bug: :bug: :bug:

Dataports now (V4sp3 update6 and V5+) clear the import record between lines. This can (AND DID at one or our customers) delete data in your database.

I didn't know this one! :shock:
But I always tell customers that need a technical upgrade to first test all before really upgrading the production database.