Filter Long Running Transaction

--DECLARE @Path varchar(100)
declare @Color char(6)
declare @cmd varchar(400)
declare @Header varchar(150)
declare @Sql varchar(1000)
declare @OutFile varchar(100)
declare @TemplFile varchar(100)
declare @DB varchar(150) ,@Rows int

--set @Path = 'HERE GOES THE PATH (CONFIDENTIAL)'

-------------------------------------------------------------------------------------------------------
-- Start Process to have the long running transaciont for all the databases
-------------------------------------------------------------------------------------------------------

IF NOT EXISTS (select * from sysobjects where type = 'u' and name = 'open_transactions_history')
Begin

CREATE TABLE dbo.open_transactions_history
(id int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
Reportingserver varchar(100) NULL,
Opentran1 varchar(500) NULL,
DB varchar(100) null,
starttime1 varchar(100) null,
loginid varchar(100) null,
Hostname varchar(300) null,
ProgName varchar(500) null,
Status varchar(100) null
)

CREATE TABLE dbo.open_transactions_tmp
( Reportingserver varchar(100) NULL,
Opentran1 varchar(500) NULL
)

END

declare DBS cursor fast_forward for
select name from master..sysdatabases
OPEN DBS

FETCH NEXT FROM DBS INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN

if DatabasePropertyEx(@DB,'Status')= null or DatabasePropertyEx(@DB,'Status')='RESTORING'
--Validate Database is not on Mirroring state, due if you database is on Mirroring mode and you run dbcc opentran on MSSQL 2005, you will get a fail msg.
print 'MirroringState'
else begin

if DATABASEPROPERTY(@DB,'IsDboOnly')=0 and DATABASEPROPERTY(@DB,'IsInRecovery')=0 and DATABASEPROPERTY(@DB,'IsOffline')=0 begin
SET @cmd = 'DBCC OPENTRAN(' + @DB + ') WITH TABLERESULTS'

truncate table open_transactions_tmp  

INSERT INTO open_transactions_tmp  
EXEC(@cmd)  

select @Rows=count(*) from open_transactions_tmp  

if @Rows>1 begin  

   insert into open_transactions_history   
   select tt.Reportingserver,tt.Opentran1,@DB,  
   (select t.Opentran1 from  open_transactions_tmp t where t.Reportingserver = 'OLDACT_STARTTIME')   
   ,s.loginame,s.hostname,s.program_name,s.status  
   from open_transactions_tmp tt inner join master..sysprocesses s on tt.Opentran1=cast(s.spid as varchar(100))  


end       

end
end

FETCH NEXT FROM DBS INTO @DB

END

CLOSE DBS
DEALLOCATE DBS

-------------------------------------------------------------------------------------------------------
-- Finish Process to have the long running transaciont for all the databases
-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------
-- NOTE:
-- After 5 minutes transcurred this open transaction still is running will be displaying on BB page.
-- Before 5 minutes transcurred this transaction gone, won't be displayed on BB Page.
-------------------------------------------------------------------------------------------------------

select @Rows=count(*) from open_transactions_history where Reportingserver='OLDACT_SPID' and DATEDIFF(minute,starttime1, getdate())>5

-------------------------------------------------------------------------------------------------------
-- Start process for creating out file for BB
-------------------------------------------------------------------------------------------------------

if @Rows=0
set @Color = 'green'
else
set @Color = 'red'

-- Generates output file Header
set @Header = @Color + convert(char(10),getdate(),103) + ' ' + convert(char(25),getdate(),108) + @@SERVERNAME + ':'
if @Color='red'
set @Header = @Header + ' SQL Long Running Transaction!'
else
set @Header = @Header + ' SQL Long Running Transaction OK!'

set @cmd = 'echo ' + @Header + ' > ' + @Path + 'sqllrt.'
exec xp_cmdshell @cmd

if @Color = 'red'
begin
set @Sql = 'select distinct cast(Opentran1 as char(5)), '
set @Sql = @Sql + ' cast(starttime1 as char(20)), '
set @Sql = @Sql + ' cast(Status as char(11)), '
set @Sql = @Sql + ' cast(DB as char(40)), '
set @Sql = @Sql + ' cast(loginid as char(25)), '
set @Sql = @Sql + ' cast(HostName as char(11)), '
set @Sql = @Sql + ' cast(progname as char(20)) '
set @Sql = @Sql + 'from open_transactions_history '
set @Sql = @Sql + 'where Reportingserver='''+'OLDACT_SPID'+''''

set @OutFile = @Path + 'sqllrt.htm'
set @TemplFile = @Path + 'sqllrt.tpl'

exec sp_makewebtask
@outputfile = @OutFile,
@query = @Sql,
@templatefile = @TemplFile,
@dbname = 'master'

set @cmd = 'type ' + @Path + 'sqllrt.htm >> ' + @Path + 'sqllrt'

exec xp_cmdshell @cmd

end

-------------------------------------------------------------------------------------------------------
-- Finish process for creating out file for BB
-------------------------------------------------------------------------------------------------------

drop table dbo.open_transactions_history
drop table dbo.open_transactions_tmp