Back to OIM Explorer

dbo.QBM_ZConnectionClearUnused

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.073 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVDatetimeToString source text reference
references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_ZConnectionClearUnused(2  @Slotnumber int,3  @Dummy1 varchar(38),4  @Dummy2 varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @dbid int10  DECLARE @spid int11  DECLARE @hostnameIntern nvarchar(64)12  DECLARE @program_name nvarchar(128)13  DECLARE @last_batchString nvarchar(64)14  DECLARE @SQLcmd nvarchar(1024),15  @leavemin int = 24016  DECLARE @ElementBuffer QBM_YCursorBuffer17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  BEGIN TRY20    IF EXISTS(21      SELECT TOP 1 122      FROM DialogDatabase23      WHERE24        SingleUserProcess <> 0 AND IsMainDatabase = 1)25    BEGIN26      GOTO ende27    END28    SELECT @dbid=database_id29    FROM sys.databases30    WHERE31      name = db_name()32    INSERT INTO @ElementBuffer(Int1,33    ContentFull,34    ContentShort,35    Ident1)36    SELECT37      spid,38      rtrim(HostName),39      rtrim(program_name),40      dbo.QBM_FCVDatetimeToString(last_batch)41    FROM sys.sysprocesses42    WHERE43      dbid=@dbid AND datediff(n,44    last_batch,45    GetDate()) >(@leavemin) AND spid IN(46    SELECT session_id47    FROM sys.dm_exec_sessions48    WHERE49      is_user_process = 1)50    SELECT @ElementCount = @@ROWCOUNT51    SELECT @ElementIndex = 152    WHILE @ElementIndex <= @ElementCount53    BEGIN54      SELECT55        TOP 1 @spid = bu.Int1,56        @hostnameIntern = bu.ContentFull,57        @program_name = bu.ContentShort,58        @last_batchString = bu.Ident159      FROM @ElementBuffer bu60      WHERE61        bu.ElementIndex = @ElementIndex62      SELECT63        @SQLcmd = N 'killing old Session HOST: ' + @hostnameIntern + N ' ; PROGRAM: ' + @program_name + N ' ; LAST ACTION: ' + @last_batchString64      INSERT INTO DialogJournal(UID_DialogJournal,65      MessageType,66      MessageString,67      MessageDate,68      ApplicationName,69      LogonUser,70      HostName)71      SELECT72        newid(),73        'W',74        @SQLcmd,75        GetUTCDate(),76        'QBM_ZConnectionClearUnused',77        LEFT(user_name(),78        64),79        LEFT(host_name(),80        64)81      SELECT82        @SQLcmd= N 'kill ' + convert(nvarchar(5),83        @spid)84      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,85        @LockTimeout_ms = 100,86        @MaxWaitTimeForLock_s = 1.0,87        @ProcIDForJournal = @@procid,88        @HandleErrorSilent = 189      SELECT @ElementIndex += 190    END91  END TRY92  BEGIN CATCH93    EXEC QBM_PSessionErrorAdd DEFAULT94    RAISERROR('',95    18,96    1)97      WITH NOWAIT98  END CATCH99  ende:100  RETURN101END

Open raw exported source

1    create   procedure QBM_ZConnectionClearUnused ( @Slotnumber int , @Dummy1 varchar(38) , @Dummy2 varchar(38) , @GenProcIDDummy varchar(38) ) 2AS begin  declare @dbid int declare @spid int declare @hostnameIntern nvarchar(64) declare @program_name nvarchar(128) declare @last_batchString nvarchar3(64) declare @SQLcmd nvarchar (1024) , @leavemin int = 240  declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int4 BEGIN TRY  if exists (select top 1 1 from DialogDatabase where SingleUserProcess <> 0 and IsMainDatabase = 1 ) begin goto ende end select @dbid=database_id5 from sys.databases where name = db_name() insert into @ElementBuffer (Int1 , ContentFull  , ContentShort  , Ident1 ) select spid, rtrim(HostName), rtrim6(program_name), dbo.QBM_FCVDatetimeToString(last_batch ) from sys.sysprocesses where dbid=@dbid  and datediff(n, last_batch,GetDate()) > (@leavemin)  and7 spid in (select session_id from sys.dm_exec_sessions where is_user_process = 1 ) select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex8 <= @ElementCount begin select top 1 @spid = bu.Int1 ,@hostnameIntern = bu.ContentFull , @program_name = bu.ContentShort , @last_batchString = bu.Ident19 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @SQLcmd = N'killing old Session HOST: ' + @hostnameIntern + N' ; PROGRAM: ' + @program_name10 + N' ; LAST ACTION: ' + @last_batchString  insert into DialogJournal (UID_DialogJournal, MessageType, MessageString , MessageDate , ApplicationName , 11LogonUser , HostName) select newid(), 'W', @SQLcmd, GetUTCDate() , 'QBM_ZConnectionClearUnused' , left(user_name(),64) , left(host_name(),64)  select @SQLcmd=12 N'kill ' + convert(nvarchar(5),@spid) exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms = 100 , @MaxWaitTimeForLock_s = 1.0 13, @ProcIDForJournal = @@procid , @HandleErrorSilent = 1 select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR 14('', 18, 1) WITH NOWAIT END CATCH ende: return end 15