Clearing Locks in Dynamics GP Manufacturing

Posted by Carla Duquette on November 10, 2016


Ever hear of database locking?  Microsoft Dynamics GP does it all the time.  Basically, a database will temporarily lock a record while someone is working in it, just to make sure no two people try and change the same record at the same time. 

It’s a good thing unless you get a phantom lock.  Sometimes, a lock can get stuck on and then you have a problem.  For example, if a user temporarily loses connection to the server, or logs out inappropriately, the lock might remain on the record as a phantom record lock. 

Most of the time you can release the lock through Dynamics GP, but sometimes that doesn’t work and you will need to go into SQL to remove the lock.  Our Crestwood support team recently helped a customer who lost connection to their server while doing a mass update to their Bill of Materials in the Manufacturing module of Dynamics GP.  Therefore, they had over 16,000 locks at one time! 

In the manufacturing module of Dynamics GP, some of the common locks are:

  • Manufacturing Orders
  • Bill of Materials
  • Route
  • Data Collection

We will go through each one of these locks and give you step-by-step directions on how to clear the locks either through the application or with the use of SQL.

Removing Manufacturing Locks – Dynamics GP & SQL Methods

Manufacturing Orders (MO)

Dynamics GP Method:  Navigate to Manufacturing > Transactions > Manufacturing Orders > Security and open the Manufacturing Order Security window. Verify the user does not have the window open or is updating the record in anyway.  To remove the lock, select the record and click on the delete button.

SQL Method:  If the record doesn't show in the above Dynamics GP window, you will need to use this SQL method.  Use the SQL “Select” statement below to identify all the locked records.

Select USERID, DEX_ROW_ID, * from MOP10223

Once you have found the locked records, use this SQL “Delete” statement to remove the locks. Make sure you replace the XXX in the delete statement with the DEX_ROW_ID you identified in the select statement of the locked record.

Delete MOP10223 where DEX_ROW_ID = 'XXX'

Bill of Materials (BOM)

Dynamics GP Method:  Navigate to Manufacturing > Transactions > Bill of Materials > Security and open the BOM Security window.  Verify the user does not have the window open or is updating the record in anyway.  Select the record and click on the delete button to remove the lock.

SQL Method:  If the record doesn't appear in the Dynamics GP window, first, use this SQL “Select” statement below to identify all the locked records. 

Select USERID, DEX_ROW_ID, * from BMS10000

Once you have found the locked records use this SQL “Delete” statement to remove the locks.  Make sure you replace the XXX in the delete statement with the DEX_ROW_ID you identified in the select statement of the locked record. 

Delete BMS10000 where DEX_ROW_ID = 'XXX'

Route

Dynamics GP Method:  Navigate to Manufacturing > Transactions > Routings > Security and open the Routing Security window. Verify the user does not have the window open or is updating the record in anyway.  To remove the lock, select the record and click on the delete button.

SQL Method:  If the record doesn't appear in the Dynamics GP window, use the following SQL “Select” statement to first identify all the locked records. 

Select USERID, DEX_ROW_ID, * from RTS10001

Once you have found the locked records use the following SQL “Delete” statement to remove the locks.  Replace the XXX in the delete statement with the DEX_ROW_ID you identified in the select statement of the locked record.

Delete RTS10001 where DEX_ROW_ID = 'XXX'

Data Collection

Dynamics GP Method:  Navigate to Manufacturing > Transactions > WIP > Security and open the Data Collection Transaction Security window. Verify the user does not have the window open or is updating the record in anyway.  To remove the lock, select the record and click on the delete button.

SQL Method:  If that doesn’t work, use this SQL “Select” statement to identify all the locked records.  

Select USERID, DEX_ROW_ID, * from SF010300

Once you have found the locked records use the SQL “Delete” statement to remove the locks.  Replace the XXX in the delete statement with the DEX_ROW_ID you identified in the select statement of the locked record.

Delete SF010300 where DEX_ROW_ID = 'XXX'

 

If you’re not familiar with SQL, or are having trouble clearing out a lock, send our team of Dynamics GP experts an email at Support@Crestwood.com and they can help you out.


Share this:
 
 

Comments:

Add your comment: