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:
Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.
Until next post!
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC