Since the DEX_LOCK and DEX_SESSION tables reside in the standard SQL Server database named tempdb, they must be created each time that database is created. Typically, this occurs each time the server is restarted.
If you are creating an application that integrates with Microsoft Dynamics GP, you will not have to create these tables. Stand-alone applications, however, must ensure these table is created each time the server is restarted.
If you use Dexterity or the runtime engine to log into a SQL data source as “sa”, and the DEX_LOCK or DEX_SESSION tables don’t exist, they will be created automatically in tempdb. Also, a stored procedure named smDex_Build_Locks that automatically re-creates these tables is added as a startup procedure. The following is the stored procedure that is added.
if exists (select * from sysobjects where id = object_id ('dbo.smDEX_Build_Locks') and sysstat & 0xf = 4) drop procedure dbo.smDEX_Build_Locks GO create procedure dbo.smDEX_Build_Locks as if not exists (select * from tempdb..sysobjects where id = object_id('tempdb..DEX_LOCK') and sysstat & 0xf = 3) begin exec ('create table tempdb..DEX_LOCK (session_id int, row_id int, table_path_name char(100))') exec ('create unique index PK_DEX_LOCK on tempdb..DEX_LOCK(row_id,table_path_name)') end if not exists (select * from tempdb..sysobjects where id = object_id('tempdb..DEX_SESSION') and sysstat & 0xf = 3) begin exec ('create table tempdb..DEX_SESSION (session_id int identity, sqlsvr_spid smallint)') exec ('create unique index PK_DEX_SESSION on tempdb..DEX_SESSION(session_id)') end exec ('use tempdb grant insert,update,select,delete on DEX_LOCK to public') exec ('use tempdb grant insert,update,select,delete on DEX_SESSION to public') return GO
The DEX_SESSION table must exist regardless of whether you implement active locking in your application. The DEX_SESSION table must exist for both active and passive locking to function properly. If you aren’t implementing active locking, you can exclude the portion of the stored procedure that creates the and sets access to the DEX_LOCK table.