Microsoft Dynamics GP Database Maintenance Utility
How many times have you performed an upgrade just to realize with the first posting that some stored procedure is missing or did not -- for some misterious reason -- get created, yet the upgrade completed with no errors? This is especially true when upgrading from several versions aback, when the upgrade path is not too clear, or you have forgotten to apply that critical service pack that would avoided some of the missing objects issues you are now experiencing.
I recently came across a user who wanted to recreate all SmartList Builder objects because they suspected something was wrong with the installation, however, they did not want to have to use the backend to recreate all the stored procs used by SLB, because of the "fair of missing something or endup damaging something else"... you know what they say... the client is always right! (not always, but that's the topic of another article).
After scratching my head for a few moments, the answer came in the form of the new Microsoft Dynamics GP Database Maintenance Utility, but first some history...
Before there was the SQL Maintenance utility, back in the old days, the former Great Plains Software provided reams of text files containing scripts to recreate any missing or damaged system and/or company database object. These scripts could be found on the installation CDs and could be accessed via the old SQL Server Query Analyzer tool. All you needed to do was to open one of script files, do a search for the desired stored procedure, copy and paste into Query Analyzer, and execute the script against the appropriate database. This sounded simple enough until system administrators (we will not call them users for fear of retaliation :-) ) began corrupting the script files and introducing their own code... It was funny, because Great Plains Support used to asked as part of their support script if you had modified X or Y stored procedure for the problem you were calling for. When Microsoft took over, came the MSIs and the script files were no longer provided. Now what? There comes SQL Maintenance, however, the set of utilities provided with SQL Maintenance did not allow you to recreate stored procedures, functions, or table triggers. These still had to be obtained from Microsoft. I guess, in the end they figured out it wasn't worth the hazzle and delivered Database Maintenance Utility.
So, lets take a look.
To recreate database objects, you will need to:
1) Launch Database Maintenance Utility. Go to Start > Programs > Microsoft Dynamics > GP 10.0 > Database Maintenance.
Select your server and select an authentication method. Fortunately, Database Maintenance can use Windows Trusted Authentication. Click Next to continue.
2) Choose the company you are going to perform the updates on. My personal preference is to choose ALL databases if you suspect a damage on a stored procedure, function or trigger. Why? Chances are, the stored procedure may be damaged across the board.
Click Next to continue.
3) Select the product for which you will like to rebuild its objects. For this example, I will use SmartList Builder.
Click Next to continue.
4) Mark the objects you would like to recreate. You have the choice of Stored Procedures (includes UDFs too), table Triggers, and Views.
Note that for Tables and table Auto Stored procedures you must use the SQL Maintenance option available within Microsoft Dynamics GP. Click Next to continue.
5) Confirm your selection.
Click on Next to continue.
6) The Utility will run through a progress bar and will return a status of the operations when completed.
If recreating stored procedures, the Database Maintenance Utility will re-grant permissions to all database users in the DYNGRP role, so no need to run the GRANT.SQL utility script.
Hopefully, you use this powerful tool as a first option in recreating any missing or damaged database object, before you call Microsoft or open that support case.
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
I recently came across a user who wanted to recreate all SmartList Builder objects because they suspected something was wrong with the installation, however, they did not want to have to use the backend to recreate all the stored procs used by SLB, because of the "fair of missing something or endup damaging something else"... you know what they say... the client is always right! (not always, but that's the topic of another article).
After scratching my head for a few moments, the answer came in the form of the new Microsoft Dynamics GP Database Maintenance Utility, but first some history...
Before there was the SQL Maintenance utility, back in the old days, the former Great Plains Software provided reams of text files containing scripts to recreate any missing or damaged system and/or company database object. These scripts could be found on the installation CDs and could be accessed via the old SQL Server Query Analyzer tool. All you needed to do was to open one of script files, do a search for the desired stored procedure, copy and paste into Query Analyzer, and execute the script against the appropriate database. This sounded simple enough until system administrators (we will not call them users for fear of retaliation :-) ) began corrupting the script files and introducing their own code... It was funny, because Great Plains Support used to asked as part of their support script if you had modified X or Y stored procedure for the problem you were calling for. When Microsoft took over, came the MSIs and the script files were no longer provided. Now what? There comes SQL Maintenance, however, the set of utilities provided with SQL Maintenance did not allow you to recreate stored procedures, functions, or table triggers. These still had to be obtained from Microsoft. I guess, in the end they figured out it wasn't worth the hazzle and delivered Database Maintenance Utility.
So, lets take a look.
To recreate database objects, you will need to:
1) Launch Database Maintenance Utility. Go to Start > Programs > Microsoft Dynamics > GP 10.0 > Database Maintenance.
Select your server and select an authentication method. Fortunately, Database Maintenance can use Windows Trusted Authentication. Click Next to continue.
2) Choose the company you are going to perform the updates on. My personal preference is to choose ALL databases if you suspect a damage on a stored procedure, function or trigger. Why? Chances are, the stored procedure may be damaged across the board.
Click Next to continue.
3) Select the product for which you will like to rebuild its objects. For this example, I will use SmartList Builder.
Click Next to continue.
4) Mark the objects you would like to recreate. You have the choice of Stored Procedures (includes UDFs too), table Triggers, and Views.
Note that for Tables and table Auto Stored procedures you must use the SQL Maintenance option available within Microsoft Dynamics GP. Click Next to continue.
5) Confirm your selection.
Click on Next to continue.
6) The Utility will run through a progress bar and will return a status of the operations when completed.
If recreating stored procedures, the Database Maintenance Utility will re-grant permissions to all database users in the DYNGRP role, so no need to run the GRANT.SQL utility script.
Hopefully, you use this powerful tool as a first option in recreating any missing or damaged database object, before you call Microsoft or open that support case.
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
Comments
Recently eXtender installed, eConnect extender stored procs missing, ran Database Maintenance Utility. It is not re-creating missing stored procs, but I can see those stored procs in TWO before I scrip it and run it manually, any advice is appreciated.
We are having the same issue with a missing company database. Did you ever get a resolution? Thanks!
I had two different service packs. Rookie mistake, but this article helped.
I am trying to use this utility after moving my GP 2010 installation to a new server but I get the following error "failed to load GPDicVer.dll".
Any ideas on how to fix this?
How did you move your installation to a new server? Did you followed the instructions in KB article 878449 (https://support.microsoft.com/en-us/help/878449)