Great Plains Document Locks

Overview

The following code is useful to find who has a document locked in Great Plains.
For example if someone has a sales document open during fulfilment it will fail... so we need to check who is in the document to get them to come out of it.

T-SQL to find who has document locks in Great Plains

USE DYNAMICS

DECLARE curLocks CURSOR
FOR SELECT  sqlSessions.login_name, LOCK.table_path_name, LOCK.row_id
 FROM    tempdb..DEX_LOCK LOCK
        INNER JOIN tempdb.dbo.DEX_SESSION SESS 
   ON LOCK.session_id = SESS.session_id        
        INNER JOIN master.sys.dm_exec_sessions sqlSessions 
   ON SESS.sqlsvr_spid = sqlSessions.session_id

DECLARE @name varchar(40)
DECLARE @table_path_name varchar(80)
DECLARE @row_id int
OPEN curLocks

FETCH NEXT FROM curLocks INTO @name, @table_path_name, @row_id
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN

  DECLARE @sql varchar(1000)
  SELECT @sql = 'select ''' + @name + ''' AS [LOGIN NAME], * from 
   ' + RTRIM(@table_path_name) + ' where dex_row_id = ' + CAST(@row_id AS VARCHAR(50))
  EXEC (@sql)

 END
 FETCH NEXT FROM curLocks INTO @name, @table_path_name, @row_id
END

CLOSE curLocks
DEALLOCATE curLocks
GO