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

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

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ’sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVPDELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ’sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVPDELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ’sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVPDELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Developing for Dynamics GP Weekly Summary

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let’s get on with the articles:

1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms. After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying any patches. Read more here.

2. Microsoft Dynamics™ GP 10.0 Bootcamp (Australia). Now, if you ever wanted to go to a Dynamics GP training class, won’t it be a dream to do so in the land down under? Now, that’s what I call a Dynamics GP training with style. Read more here.

3. eOne.Dynamics.GP.ExcelBuilder.Engine.dll Exception. If you have received this error after applying Microsoft Dynamics GP 10 Service Pack 2 and above and you are currently using SmartList Builder and Excel Builder, make sure you take a look at this article and the fix by reading more here.

4. Hybrid – Adding Named Printers control to Reports using VBA. Wondering how to get Named Printers to work with unsupported reports? David answers this one with a cool VBA customization. Best of all, you can download sample code! To play with this code, click here.

5. WorldMaps Tracking Added. As if my uber friend wasn’t all that uber geek already, he adds a cool hits tracker on his blog with the cool WorldMaps beta product from Structure Too Big. I have to confess, I will be adding this one pretty soon to my blog! I find it fascinating to know where your readers are concentrated, which helps when developing content for the blog. For more info, click here.

6. Modifier – Reading and Writing Data with ADO Example. It does not matter how many of these we do, I always find Modifier with VBA fascinating and a very good alternative to Dexterity customizations when possible. Now, don’t get me wrong. I love Dexterity, but I also love the portability offered by VBA customizations too. To read more, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Developing for Dynamics GP Weekly Summary

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let’s get on with the articles:

1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms. After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying any patches. Read more here.

2. Microsoft Dynamics™ GP 10.0 Bootcamp (Australia). Now, if you ever wanted to go to a Dynamics GP training class, won’t it be a dream to do so in the land down under? Now, that’s what I call a Dynamics GP training with style. Read more here.

3. eOne.Dynamics.GP.ExcelBuilder.Engine.dll Exception. If you have received this error after applying Microsoft Dynamics GP 10 Service Pack 2 and above and you are currently using SmartList Builder and Excel Builder, make sure you take a look at this article and the fix by reading more here.

4. Hybrid – Adding Named Printers control to Reports using VBA. Wondering how to get Named Printers to work with unsupported reports? David answers this one with a cool VBA customization. Best of all, you can download sample code! To play with this code, click here.

5. WorldMaps Tracking Added. As if my uber friend wasn’t all that uber geek already, he adds a cool hits tracker on his blog with the cool WorldMaps beta product from Structure Too Big. I have to confess, I will be adding this one pretty soon to my blog! I find it fascinating to know where your readers are concentrated, which helps when developing content for the blog. For more info, click here.

6. Modifier – Reading and Writing Data with ADO Example. It does not matter how many of these we do, I always find Modifier with VBA fascinating and a very good alternative to Dexterity customizations when possible. Now, don’t get me wrong. I love Dexterity, but I also love the portability offered by VBA customizations too. To read more, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Developing for Dynamics GP Weekly Summary

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let’s get on with the articles:

1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms. After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying any patches. Read more here.

2. Microsoft Dynamics™ GP 10.0 Bootcamp (Australia). Now, if you ever wanted to go to a Dynamics GP training class, won’t it be a dream to do so in the land down under? Now, that’s what I call a Dynamics GP training with style. Read more here.

3. eOne.Dynamics.GP.ExcelBuilder.Engine.dll Exception. If you have received this error after applying Microsoft Dynamics GP 10 Service Pack 2 and above and you are currently using SmartList Builder and Excel Builder, make sure you take a look at this article and the fix by reading more here.

4. Hybrid – Adding Named Printers control to Reports using VBA. Wondering how to get Named Printers to work with unsupported reports? David answers this one with a cool VBA customization. Best of all, you can download sample code! To play with this code, click here.

5. WorldMaps Tracking Added. As if my uber friend wasn’t all that uber geek already, he adds a cool hits tracker on his blog with the cool WorldMaps beta product from Structure Too Big. I have to confess, I will be adding this one pretty soon to my blog! I find it fascinating to know where your readers are concentrated, which helps when developing content for the blog. For more info, click here.

6. Modifier – Reading and Writing Data with ADO Example. It does not matter how many of these we do, I always find Modifier with VBA fascinating and a very good alternative to Dexterity customizations when possible. Now, don’t get me wrong. I love Dexterity, but I also love the portability offered by VBA customizations too. To read more, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

New article on MSDynamicsWorld: 5 Tips for a Smooth Upgrade to Microsoft Dynamics GP 10

“Avoid the temptation to take shortcuts”

For those of you who follow me on MSDynamicsWorld, after a small writing sabatical I am back with a new article on upgrading to Dynamics GP 10.0. My key recommendations: work from the FP1 DVD, test several times, make sure you know your upgrade path, take advantage of the latest Microsoft OS and database technology, but foremost, work with your partner!

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

New article on MSDynamicsWorld: 5 Tips for a Smooth Upgrade to Microsoft Dynamics GP 10

“Avoid the temptation to take shortcuts”

For those of you who follow me on MSDynamicsWorld, after a small writing sabatical I am back with a new article on upgrading to Dynamics GP 10.0. My key recommendations: work from the FP1 DVD, test several times, make sure you know your upgrade path, take advantage of the latest Microsoft OS and database technology, but foremost, work with your partner!

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/