The topic for this month shows you where you can look to get a list of users that are logged into Sage 500 AND how to check if there any blocked / blocking connections. I will demonstrate an example of a blocked / blocking transaction, to show you what it would look like if you were to run into that scenario. The task is System Status, located under System Manager, Tools. By the way, you would also need to have security permission to be able to launch this task. ( System Manager, Maintenance, Maintain Security Groups, then select ModuleID = SM and look for the System Status in the Task Description column)
Once you have taken care of the security permissions, launch this task you will see the main screen.
The Options and Settings tab, shows a couple of options that can be toggled on/off.
Show only Sage ERP MAS 500 Connections
If you do not check this option and click the Refresh icon, the Connections tab now has focus. The Connections tab shows a list all the process IDs, statuses, users, and other connection information, as shown below. In this particular instance, we see a list of process ids that connected to the same SQL Server and database as the Sage 500 application. How can you tell if the Process ID is running a task from within the Sage 500 application or not? If you look at the Connections tab, and scroll to the right, you will see an Application heading. If the name of the application begins with "Sage ERP", then that particular ProcessID is using one of the tasks in the Sage 500 application.
In fact, if you look at the application name, you can see the name of the task that is currently being used by that user, example below shows the Completion Entry task in Manufacturing. If the application name begins with something other than "Sage ERP MAS ", the session is coming from outside the Sage 500 application. In the example below you can see a couple of ProcessIDs connected to the same SQL Server same database, from SQL Server Management Studio.
If you go back to the Options and Settings tab and place a checkmark next to Show only Sage ERP MAS 500 connections option, click Refresh and now you will only see Process IDs that are from the Sage 500 application.
Show only blocking and blocked connections
This option can be useful to find connections that are being blocked and connections that are blocking other connections. Once this option is checked and Refresh is clicked, the Connections tab will show a list of blocked and blocking sessions. I created a blocked / blocking scenario to simulate what the System Status screen would look like below.
There are two rows shown. These are the ProcessIDs involved in the blocked / blocking scenario. Notice that the Blocked column shows 61 and 0. This tells us that the ProcessID 64, is being blocked by ProcesssID 61. ProcessID 61 is the blocking process and ProcessID 64 is the blocked process. If you look at the Application column, you will see the name of the applications involved in the blocking. In my example, of course, it will show Microsoft SQL Server Management Studio Query. In this example then, you know the blocking is not happening from within the Sage 500 application.
How can these blocked / blocking connections be cleared?
Once you have identified the blocking ProcessID(the one causing the problem), how can it be cleared? Look at the application that is currently running and contact the user to have them close down the task by completing the process(post, print, respond to a message box). If the task cannot be closed by regular means, find out if it is a process that normally takes a long time to complete. If so, try letting it complete on its own. If the process is going to take too long and needs to be stopped, try using task manager from the workstation where the process is running. (Check the Hostname column to get the workstation name). After the task has ended, click Refresh as that should clear the connections involved in this scenario.
For those savvy in SQL Server, you know there are other ways to find this information. Otherwise, I hope you can use this task if you ever have a need to troubleshoot blocked / blocking tasks to deterine which ones are involved.
-Tony