GP Database Maintenance Utility

Problem today with attempting to run the GP Database Maintenance utility, however it was only listing DYNAMICS and no other companies installed on the database server.

I took a look at what the window was up to with the database, to see if permissions were an issue. The following SQL is being executed around this window, but the user has Sysadmin rights, so I’m baffled as to why this wont work.

There are posts on Google about making certain other users are not logged into the databases, however even waiting until out of hours, with no users, gave the same result.

select [name] from [master]..[sysdatabases]

if ( ( OBJECTPROPERTY ( OBJECT_ID('DB_Upgrade'), 'IsTable' ) = 1)) select 1 else select 0
if ( (select [db_status] from [DB_Upgrade] where [db_name]='DYNAMICS' and PRODID=0) = 0) 
select RTRIM(LTRIM(STR(db_verMajor))) + '.' + RTRIM(LTRIM(STR(db_verMinor))) + 
'.' + RTRIM(LTRIM(STR(db_verBuild))) from DB_Upgrade 
where db_name = 'DYNAMICS' and PRODID = 0 else select RTRIM(LTRIM(STR(db_verOldMajor))) + '.' + RTRIM(LTRIM(STR(db_verOldMinor))) + '.' +
RTRIM(LTRIM(STR(db_verOldBuild))) from DB_Upgrade where db_name = 'DYNAMICS' and PRODID = 0

if (IS_SRVROLEMEMBER ('sysadmin')) = 1 BEGIN select 1 END ELSE BEGIN select 0 END

But it works on the server

First port of call on these kind of issues is to go try it on a machine that is not used for software development as the GP install does tend to get hacked around a lot in developing solutions. Launching the utility on the server worked correctly, so a local install issue. I performed a diff between the two application folders and files. After twenty minutes I had it narrowed down to the DYNUTILS.SET file. When this file was copied from the server, the utility started working.

Performing a file diff on the SET file revealed the problem.

The manufacturing module reference had been taken out of the local SET file, but that alone should not cause this issue. Look, the left hand diff summary visualisation shows only the DIC count and the DIC declaration to be changed between the two files. What about the file path pointers later on in the SET file? I would expect to see three groups of changes to remove a module. Three lines for the Manufacturing module .DIC references to the files should have been taken out of the SET file. This turns out to be the source of the problem. When the reference to the module was removed at the top of the file, the reference to the DIC files had not been removed, leaving a corrupt SET file.

We can see from the first image that manufacturing module ID is 346, so removing the following lines and then saving the SET file made everything good again.

:C:Program Files (x86)/Microsoft Dynamics/GP2013/du346.dic
:C:Program Files (x86)/Microsoft Dynamics/GP2013/Data/DU346F.DIC
:C:Program Files (x86)/Microsoft Dynamics/GP2013/Data/DU346R.DIC

Result: Now we can see all the companies (if I hadn’t blurred them out)…

As it happens I’ve sorted out the issue I needed the Database Maintenance Utility for in the first place, but others may find this helpful.

Please leave a comment if you found this helpful, as comments motivate me to write more!