Creating the DEX_LOCK and DEX_SESSION tables

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

Documentation Feedback

---