Is Navision 3.6 Compatible with SQL Server 2005?

nbrewer

After successfully restoring our clients database from SQL Server 2000 to SQL Server 2005, I attempt to connect and receive the following error in Navision:

The Session table contains a Navision Attain field data type that is not compatible with the SQL Server data type:

Field: Wait Time (ms)
Type: Integer
SQL type: BIGINT

I looked at all the tables in the database and [SESSION] did not show up in the list. However, using QA I can select and view all records from [SESSION]. It seems it’s a hidden table. I also suspect, if errors are being thrown on that table, then other tables with have some incompatibilities as well.

Is 3.6 compatible with SQL Server 2005? How about Navision 4.0?

TIA

DenSter

The ONLY Navision version currently supported on SQL Server 2005 is 4.0 Service Pack 1. Within a few weeks Service Pack 2 is coming out, and that will also be supported on SQL Server 2005. All other Navision versions are not supported on that version of SQL Server, although quite a few people say they have previous versions running successfully on that platform.

sfi

You can work around this error.

1. Start Management Studio and connect to your SQL Server.
2. Expand Databases, expand the Navision Database, and expand Views.
3. Right Click on the View dbo.Session and in the Drop Down Menu select
Script view as --> Create to --> New Editor Window.
4. Locate "THEN SP.[waittime]" and change the code to
"THEN CAST(SP.[waittime] AS INT)" (without ")
5. As the view already exists you have to change the CREATE VIEW
command to ALTER VIEW.

You can then run Navision at least 3.7 on SQL Server 2005, but there is another issue. That is the user access. It has so far only been possible to access the database as SQL Server 2005 sysadmin, other users get the message: "The user does not have permission to perform this action." I'm working to solve that issue.

ara3n

It's not worth the troublel to use older version of navision on SQL2005. Just do an executable upgrade and you are done.
Unless ofcourse because of licensing issue.

DenSter

I agree, a tech upgrade will probably save you a lot of problems. Your solution center should be able to give you a quote on that.

BMCI

Hi,

I'm having the same issue, I worked around the CAST but now there's no way I can use any other user rather than my sa account on a NAV3.70 on SQL2005; any other attempts end up with "Error 297, User does not have permission to perform this action" while SQL was accessing the table Session.

Has anyone (particularly sfi) found any way to enable any other user to access the db under the mentioned environment?

Thanks for your help.

DenSter

Yes the solution is delete the database, upgrade the executables to 4.0 SP3 and recreate the database.

3.6 is not supported on SQL Server 2005

BMCI

Sorry DenSter, I don't think I got your point. Are you saying that all I have to do are these three steps?:

1. Delete the database
2. Replace 3.70's fin.exe and finsql.exe files with those from 4.00SP3 -where, on server and all clients?
3. Create a new database and restore the fbk I got from my old W2K

Does that also include the CAST workaround before installing everything?

Thanks again.

DenSter

Don't even go near any NAV stored procedures. Those are not meant to be modified. If something goes wrong, you will have a hard time getting support from MSFT. I would personally NOT use the workaround, and I don't care if it works or not.

What I am saying is that 3.6 is not supported on SQL Server 2005. Either you do a technical upgrade to a version that IS supported, or you downgrade your SQL Server to 2000.

When you do a technical upgrade, you upgrade all instances of NAV software, including all clients, all database servers (if you use those) all NAS's, everything. It all must be installed from the same product CD.

This is not everything you need to know. Get your solution center involved to guide you through the technical upgrade.

BMCI

Th

BMCI

Thanks again for your help. I needed to know what the chances were to stick to 3.70, since we've carried out some mods and this could complicate any application upgrade considerably, taking longer. Now I know what to do... #-o

Have a nice day.

DenSter

I am talking about a technical upgrade, of executables only. You can create a new database with a newer version of the executables, and restore your backup into it. This will result in a database that behaves exactly the same, but on the newer executables.

If I were you, I'd your solution center involved, you clearly need some assistance.

BMCI

...At least from the server part.

Our IT guys conceeded a GRANT VIEW SERVER STATE TO PUBLIC on other users than sysadmin. Now it works, as I said, at least from the server side. Let's see if I can now run a good number of tests to check the executables part you talked about.

DenSter

I can't stop you if you are set on running an unsupported version on SQL Server 2005 with a hack on a production environment. Just know that if it does go wrong, you will have caused massive financial losses, because you will be charged for every hour that is spent on fixing your system.

Do yourself a favor and listen to the advice. Do a technical upgrade, or use SQL Server 2000, it will save you money in the long run.

easy-navi

DenSter, I have the same problem (nav 3.7, sql2005). So please tell us - how should be done a technical upgrade of executables:

Method I
1. Client 3.7, connect to sql2005
2. create database on sql
3. restore db from *.fbk
4. Install Nav 4.3 on a client
5. connect with 4.3client to that db
6. System will convert the db n3.7-> n4.3

OR rather:

Method II
1. install the 4.3 client
2. connect to the sql2005 , create new database
3. Restore db from fbk
4. connect with 4.3client to that db

My problem is that a have already made points 1..3 from the first method. Now I am not sure
if i will have to restore the db second time. (it is restoring very very long:(

Jack,

DenSter wrote:

I am talking about a technical upgrade, of executables only. You can create a new database with a newer version of the executables, and restore your backup into it. This will result in a database that behaves exactly the same, but on the newer executables.

If I were you, I'd your solution center involved, you clearly need some assistance.

ara3n

I suggest to go with method 2. 3.7 is not compatible with 2005.