Locked Record by a User (MS GP10)

While testing an integration package called Scribe to transfer data between our MS CRM and MS Dynamics GP 10, I came across an interesting problem. Sometimes, the Scribe adapter for GP will lock a sales order record. So far it appears a random one and one that is not in CRM.

The problem is that when a user enters this record to use it, their GP client freezes and crashes. This then leaves the record locked in the SY00800 table in the Dynamics database.

If you know the record details, you can free it using this method;

DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION

Run the following script against the company database. Replace XXX with the batch number or the name of the batch that you are trying to post or select in Microsoft Dynamics GP.

UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB='XXX'
(Thanks to Raymond Wong at Candlewest for this)
Note: In my case, there was no entry to update in the SY00500 table.

But if not, then try this SQL statement to show current sales order record locks.

SELECT 'Sales Documents' as LockType, DexSession.sqlsvr_spid , ISNULL(sysproc.loginame, 'Disconnected') as LoginName,
SOP10100.SOPNUMBE as SOPNumber,

CASE SOP10100.SOPTYPE WHEN 1 THEN 'Quote' WHEN 2 THEN 'Order' WHEN 3 THEN 'Invoice'
WHEN 4 THEN 'Return' WHEN 5 THEN 'Back Order' WHEN 6 THEN 'Fulfillment Order' else 'Unknown' END as SOPType,

SOP10100.CUSTNMBR as CustNumber

FROM tempdb..DEX_LOCK DexLock

INNER JOIN tempdb..DEX_SESSION DexSession ON DexLock.session_id = DexSession.session_id

INNER JOIN SOP10100 ON DexLock.row_id = SOP10100.DEX_ROW_ID

LEFT OUTER JOIN master..sysprocesses sysproc ON DexSession.sqlsvr_spid = sysproc.spid

WHERE DexLock.table_path_name = RTRIM(DB_NAME()) + '.dbo.SOP10100'


-SQL created by Ron Draganowski in the post "User tool to identify a locked record"