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

darkmark07darkmark07 Member Posts: 7
edited 2013-12-17 in Navision Attain
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

Comments

  • darkmark07darkmark07 Member Posts: 7
    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 :-(
  • krikikriki Member, Moderator Posts: 9,039
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • darkmark07darkmark07 Member Posts: 7
    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...
  • krikikriki Member, Moderator Posts: 9,039
    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).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • darkmark07darkmark07 Member Posts: 7
    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
  • krikikriki Member, Moderator Posts: 9,039
    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!
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • darkmark07darkmark07 Member Posts: 7
    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!
  • krikikriki Member, Moderator Posts: 9,039
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • darkmark07darkmark07 Member Posts: 7
    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
Sign In or Register to comment.