To Identify blocks if using Solomon IV

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




/*Authored by Bob Hauck
	Desc: Our business accounting software is Microsoft Business Solutions Solomon IV.
which is now being called Dynamics Solomon.  You will see specific refernces to this 
software in this code. I originally developed this to gain faster view of issues we were 
having with Solomon.
	This procedure analyzes system tables and looks for blocks.  This is
faster than using enterprise manager to look for blocks on the system.
This can be called from any database if you install in master.  This proc will look at system
tables and execute DBCC INPUTBUFFER to attempt to get some idea of what is happening in the
lock contention.  
	If you are running Solomon you can pass the Solomon System Database name as parameter 1 and 
it will look up user contact info such as phone number and pass that info on.
Contact Info:
	bob.hauck@tdindustries.com
	972-888-9567
*/

CREATE PROCEDURE sp_Current_Blocks @Solomon_Sys_DB_Name varchar(100) = NULL AS
Set NoCount ON
Declare @WorkStation as varchar(8000),
 	@SPID as smallint,
 	@NT_UserName as varchar(8000)

Create table #temp (	spid smallint, 
			TreadID smallint,
			Blocked_by smallint,
			Wait_Type binary(2),
			Wait_Time int,
			Last_Wait_Type nchar(32),
			Wait_Resource nchar(256),
			Database_ID smallint,
			UserID smallint,
			CPU int,
			Physical_IO bigint,
			Memory_Usage int,
			Log_in_Time datetime,
			Last_Batch datetime,
			Execution_Context smallint,
			Open_Transactions smallint,
			Status nchar(30),
			GUID_or_sid binary(86),
			WorkStation nchar(128),
			Application nchar(128),
			WorkStation_PID nchar(8),
			Current_Command nchar(16),
			NT_Domain nchar(128),
			NT_UserName nchar(128),
			net_address nchar(12),
			net_library nchar(12),
			Login_Name nchar(128),
			[context_info] binary(128),
			SQL_Handle binary(20),
			stmt_start int,
			stmt_end int
		    )

insert #temp 
Select * from master.dbo.sysprocesses (NoLock) where blocked <> 0 or spid in (
	Select blocked from master.dbo.sysprocesses (NoLock) where blocked <> 0)

Select 	@Workstation = Workstation, 
	@SPID = spid ,
	@NT_UserName = nt_username
from #temp where Blocked_by = 0
----------------------------------------------------------------------------------------------
Update #temp set stmt_end = 0

Declare @cmd as varchar(2000),
	@spid2 as int

CREATE TABLE #TempBuffer (
	[EventType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Parameters] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EventInfo] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[spid] [int] NULL
)

While Exists(Select * from #temp where stmt_end = 0)
Begin
	Select Top 1 @spid2 = spid from #temp where stmt_end = 0
	Set @cmd = 'DBCC INPUTBUFFER (' + Convert(char,@spid2) + ') WITH NO_INFOMSGS '
	Insert into #TempBuffer(EventType, Parameters, EventInfo) EXEC( @cmd)

	Update #TempBuffer Set spid = @spid2 where spid is null

	Update #temp set stmt_end = 1 where spid = @spid2

End

----------------------------------------------------------------------------------------------

	CREATE TABLE #Temp2 (
		[Status] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[NT_UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[Workstation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[SolomonUserID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[Phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[spid] [int] NULL ,
		[ProgramName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
		[Buffer] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
		[Blocked_by] [int] NULL 
	)
If @Solomon_Sys_DB_Name is not null
Begin
	Insert #Temp2 ( Status, NT_UserName, WorkStation, SolomonUserID, Phone, spid, ProgramName,Blocked_By)
	EXEC   ('Select Case when Blocked_by = 0 ' + 
		'Then ''Blocking others'' ' + 
		'Else ''Is Blocked by '' + ltrim(rtrim(Convert(char,a.Blocked_by))) + ''-'' + ltrim(rtrim(IsNull(b.UserID, ''Not Solomon''))) ' + 
		'End, ' + 
		'ltrim(rtrim(NT_UserName)), ' + 
		'ltrim(rtrim(WorkStation)), ' + 
		''''', ' + 
		'Case Len(IsNull(c.Phone,Isnull(d.Phone,''''))) ' + 
			'When 10 Then Substring(IsNull(c.Phone,Isnull(d.Phone,'''')),1,3) + ' + 
						'Substring(IsNull(c.Phone,Isnull(d.Phone,'''')),4,3) ' + 
						' + ''-'' + Substring(IsNull(c.Phone,Isnull(d.Phone,'''')),7,4) ' + 
			'Else IsNull(c.Phone,Isnull(d.Phone,'''')) end, ' + 
		'spid , ' + 
		'Application, ' + 
		'a.Blocked_by ' + 
	'From #temp a ' + 
	'Left Outer Join (Select InternetAddress, UserID From ' + @Solomon_Sys_DB_Name + '.dbo.access ' + 
				'Group by InternetAddress, UserID) b ' + 
		'on a.Workstation = b.InternetAddress ' + 
	'Left Outer Join (Select UserID, Phone from ' + @Solomon_Sys_DB_Name + '.dbo.userrec) c on b.userid = c.userid ' + 
	'Left Outer Join (Select UserID, Phone from ' + @Solomon_Sys_DB_Name + '.dbo.userrec) d on a.NT_UserName = d.userid ' + 
	'order by blocked_by')
	
	Update x
		Set Buffer = EventInfo
	From #Temp2 x
	Join #TempBuffer y on x.spid = y.spid
	
	Select * from #Temp2
	Order by Status, blocked_by
End
Else
Begin
	Insert #Temp2 ( Status, NT_UserName, WorkStation, SolomonUserID, Phone, spid, ProgramName,Blocked_By)
	Select Case when Blocked_by = 0  
		Then 'Blocking others'
		Else 'Is Blocked by ' + ltrim(rtrim(Convert(char,a.Blocked_by)))
		End,
		ltrim(rtrim(NT_UserName)), 
		ltrim(rtrim(WorkStation)),
		'',
		'',
		spid ,
		Application,
		a.Blocked_by 
	From #temp a
	order by blocked_by
	
	Update x
		Set Buffer = EventInfo
	From #Temp2 x
	Join #TempBuffer y on x.spid = y.spid
	
	Select Status, NT_UserName, Workstation, SPID, ProgramName, Buffer, Blocked_By
	from #Temp2
	Order by Status, blocked_by

End

drop table #temp
drop table #Temp2
drop table #TempBuffer

Set NoCount OFF


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO