Migration of Nav 4.0 SQL from SQL 2000 > SQL 2008 R2

darkmark07

Hi all.

We're in the process of consolidating our servers and stopped using Navision some time ago.

I'd like to move the existing Navision Attain 4.0 database from SQL Server 2000 to our new SQL Server 2008R2 platform.

So far, I've run into problems with extended stored procedure xp_ndo_enumusergroups.

I've managed to copy across the dll file and create a new extended stored procedure on the new server but the client reports a problem:

"Could not load the DLL [correct path to dll file] or one of the DLLs it references. Reason 193(failed to retrieve text for this error. Reason: 15105).
SQL:
INSERT INTO [#$ndo$groups] {CALL [master]..[xp_ndo_enumusergroups](?,?)}"

Did I miss something?

Thanks in advance,

Mark

darkmark07

OK, bit of an update. Improved Google-Fu produced this article which explains about granting the Execute permission to the public user: http://blogs.msdn.com/b/nav_developer/archive/2008/11/05/basic-sql-creating-extended-stored-procedure-xp-ndo-dll.aspx
I also dug around at the back of a cupboard and found the Navision disks with the 64-bit version of the dll.

Unfortunately, even after following the instructions and using the 64-bit dll, I get the same behaviour.

I also restarted the SQL server service as that was mentioned in another article as having helped after the stored procedures were created.

Still no joy :-(

kriki

I am not sure you can do that with a NAV 4.0 DB. Probably you need 4.0SP3 for it.

Try first with this TSQL (changing paths of course).
PS even if you are using the 64-bit dll, you NEED to rename it to "xp_ndo.dll".

-- create stored procedures of xp_ndo.dll

USE master
EXEC sp_addextendedproc xp_ndo_enumusergroups, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo.dll'
GO

GRANT EXECUTE
ON [xp_ndo_enumusergroups]
TO PUBLIC
GO

USE master
EXEC sp_addextendedproc xp_ndo_enumusersids, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo.dll'
GO

GRANT EXECUTE
ON [xp_ndo_enumusersids]
TO PUBLIC
GO 

GRANT EXECUTE
ON [xp_ndo_enumusergroups]
TO PUBLIC

If this doesn't work, you have 2 options: do a technical upgrade to 4.0SP3 client (meaning : open the database with the new client and convert it. It should not give problems with the license).

Another possibility is to use only db-logins. You create a SQL database login and then you create the user in NAV (Tools=>Security=Database Logins). This way the DLL's are not needed. The negative is that when a user opens NAV, he needs to give the db-login+PW.

darkmark07

Thanks Alain.

I did as you said; unfortunately still the same.

Out of interest, is it possible that the 64-bit dll (file date 29/08/2005) included with Navision 4.0 SP1 is not for the x64 platform, but for something else?

I only ask as if I try to register the dll using regsvr32 (just out of curiosity), I get an error telling me that it is not supported on this platform...

kriki

I never tried, but it is very well possible the 4.0SP1 dll does not work on a newer Windows (I suppose you are using at least Windows 2008R2).

You can always use the version from 2009R2.

But as I wrote before, I am not sure you can make it work with NAV40SP1. I checked what I did on our servers and I noticed I always used 4.0SP3-client also for the older CRONUS-DB's (on Windows 2008R2 and SQL2008R2).

darkmark07

Thanks again Alain.

I will try to get hold of a copy of the more recent dll to try it out. Unfortunately, our use of Navision ceased around 4 years ago, as did our support contract but I have a few contacts who might be able to help me out in this direction.

I accept your point regarding SP3, and maybe that's a way that we have to go but it's less hassle to try the newer dll first before moving to the upgrade.

Regardless, if I come to a satisfactory conclusion, I will post the solution here.

Cheers,

Mark

kriki

You can always go the DB login-way:No need for the dll!

PS: NAV 4.0SP1 client does NOT work with Win 2008 (R2),Vista, W7,W8!

darkmark07

Agreed, however we still have a number of user roles in place, all of which are currently assigned via AD Groups. It would be a reasonable amount of work to recreate this using DB logins, but is a possible avenue of last resort.

It seems amazing to me that a read-only MRP system that has been out of use for nearly 4 years is still considered a very important source of information in this organisation. I can understand the requirement to have an archive for legal and compliance reasons especially for finance, but the level of use of this venerable system throughout all areas of the business is surprising, to say the least!

kriki

Maybe a stupid idea, but I throw it in anyway:

How about creating a small VM with the old SQL Server and an old Windows (even virtualizing the old server and throw all that doesn't serve for NAV away)?
I don't think that server will be used so much.

And like you said: very weird it is still used a lot for info. Maybe it should be taken out and put into some other system.

darkmark07

The existing NAV server is virtualised. This is more about consolidating SQL licenses / backup & restore effort than anything else.

Regardless, I need to preserve the data intact for compliance purposes for at least another 3 years so up to that point, we need a (functional on-demand) NAV system for that.

Beyond that, if someone can tell me exactly what 7-year-old data is business-critical, then we can look at how to extract and store that data in a useful way. In the meantime I'll see what I can do to bring NAV4 and SQL 2008R2 together

Thanks again.

Mark