Back to OIM Explorer

dbo.QBM_PJobQueueStatsShrink

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 2.639 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_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PDeleteBulk source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL114 lines
1CREATE PROCEDURE QBM_PJobQueueStatsShrink2AS3BEGIN4  DECLARE @datum datetime5  SELECT @datum = GetUTCDate()6  DECLARE @maxAge int7  DECLARE @MaxDate datetime8  DECLARE @DeleteWhereClause nvarchar(max)9  DECLARE @AnzahlGeloescht int10  DECLARE @AnzahlGesamt int = 011  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')12  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')13  DECLARE @GenProcID varchar(38) = dbo.QBM_FGISessionContext('')14  DECLARE @XUser nvarchar(64) = dbo.QBM_FGISessionContext('XUser')15  SET XACT_ABORT OFF16  BEGIN TRY17    IF EXISTS(18      SELECT TOP 1 119      FROM DialogDatabase20      WHERE21        SingleUserProcess <> 0 AND IsMainDatabase = 1)22    BEGIN23      GOTO GanzEnde24    END25    IF isnull(@XUser,26    '') = ''27    BEGIN28      SELECT @XUser =29      LEFT(Object_name(@@procid),30      64)31      EXEC QBM_PSessionContextSet 'GenProcID',32        @GenProcID33      EXEC QBM_PSessionContextSet 'XUser',34        @XUser35    END36    SELECT37      @maxAge = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\JobQueueStats\MaxAge'),38      0)39    IF @maxAge = 040    BEGIN41      SELECT @maxAge = 3042    END43    SELECT44      @maxdate = dateadd(dd,45      @maxage *(-1),46      GetUTCDate())47    SELECT48      @DeleteWhereClause = N 'convert(datetime,convert(nvarchar(4), jobyear) + N''-'' + right(N''0'' + convert(nvarchar(2), jobmonth), 2) + N''-'' +  right(N''0''+ convert(nvarchar(2), jobday) , 2) , 121)  49								< '''50      + convert(nvarchar(64),51      @maxdate,52      121) + '''  /*@maxdate */ '53    EXEC @AnzahlGeloescht = QBM_PDeleteBulk 'jobqueuestats',54      @DeleteWhereClause,55    50056    SELECT @AnzahlGesamt += @AnzahlGeloescht57    SELECT @GenProcID = newid()58    EXEC QBM_PSessionContextSet 'GenProcID',59      @GenProcID60    EXEC QBM_PSessionContextSet 'XUser',61      @XUser62    INSERT INTO JobQueueStats63      WITH(tablock)(UID_JobQueueStats,64    queue,65    jobYear,66    jobMonth,67    jobDay,68    jobHour,69    countInserted,70    countActivated,71    countDeleted)72    SELECT73      newid(),74      queue,75      jobYear,76      jobMonth,77      jobDay,78      jobHour,79      abs(sum(countInserted)),80      abs(sum(countActivated)),81      abs(sum(countDeleted))82    FROM jobqueuestats83    WHERE84      uid_jobqueuestats LIKE N 'XX%' AND jobHour <> datepart(hh,85    @datum)86    GROUP BY queue,87    jobYear,88    jobMonth,89    jobDay,90    jobHour91    SELECT92      @DeleteWhereClause = N 'uid_jobqueuestats like N''XX%'' 93								and jobHour <> ' + convert(nvarchar(16),94      datepart(hh, @datum))95    EXEC @AnzahlGeloescht = QBM_PDeleteBulk 'jobqueuestats',96      @DeleteWhereClause,97    50098    SELECT @AnzahlGesamt += @AnzahlGeloescht99  END TRY100  BEGIN CATCH101    EXEC QBM_PSessionErrorAdd DEFAULT102    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()103    RAISERROR(@Rethrow,104    18,105    1)106      WITH NOWAIT107  END CATCH108  ende: GanzEnde:109  EXEC QBM_PSessionContextSet 'GenProcID',110    @GenProcID_R111  EXEC QBM_PSessionContextSet 'XUser',112    @XUser_R113  RETURN(@AnzahlGesamt)114END
Open raw exported source
SQL ยท Raw19 lines
1     create   procedure QBM_PJobQueueStatsShrink  as begin   declare @datum datetime select @datum = GetUTCDate() declare @maxAge int declare @MaxDate2 datetime  declare @DeleteWhereClause nvarchar(max) declare @AnzahlGeloescht int declare @AnzahlGesamt int = 0 declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext3('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser nvarchar4(64) = dbo.QBM_FGISessionContext('XUser') SET XACT_ABORT OFF BEGIN TRY  if exists (select top 1 1 from DialogDatabase where SingleUserProcess <> 0 and 5IsMainDatabase = 1 ) begin goto GanzEnde end  if isnull(@XUser, '') = '' begin select @XUser = left(Object_name(@@procid), 64) exec QBM_PSessionContextSet6 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser end select @maxAge = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\JobQueueStats\MaxAge'7), 0) if @maxAge = 0 begin select @maxAge = 30 end select @maxdate = dateadd(dd, @maxage * (-1) , GetUTCDate())  select @DeleteWhereClause = N'convert(datetime,convert(nvarchar(4), jobyear) + N''-'' + right(N''0'' + convert(nvarchar(2), jobmonth), 2) + N''-'' +  right(N''0''+ convert(nvarchar(2), jobday) , 2) , 121)  8								< '''9 + convert(nvarchar(64), @maxdate, 121) + '''  /*@maxdate */ ' exec @AnzahlGeloescht = QBM_PDeleteBulk 'jobqueuestats', @DeleteWhereClause, 500 select 10@AnzahlGesamt += @AnzahlGeloescht  select @GenProcID = newid() exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser',11 @XUser  insert into JobQueueStats with (tablock) (UID_JobQueueStats, queue , jobYear, jobMonth , jobDay , jobHour , countInserted, countActivated, countDeleted12 ) select newid(), queue , jobYear, jobMonth , jobDay , jobHour , abs(sum(countInserted)), abs(sum(countActivated)), abs(sum(countDeleted)) from jobqueuestats13 where uid_jobqueuestats like N'XX%' and jobHour <> datepart(hh, @datum) group by queue , jobYear, jobMonth , jobDay , jobHour  select @DeleteWhereClause14 = N'uid_jobqueuestats like N''XX%'' 15								and jobHour <> ' + convert(nvarchar(16) , datepart(hh, @datum)) exec @AnzahlGeloescht = QBM_PDeleteBulk 16'jobqueuestats', @DeleteWhereClause, 500 select @AnzahlGesamt += @AnzahlGeloescht END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow17 varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: GanzEnde: exec QBM_PSessionContextSet 'GenProcID'18, @GenProcID_R exec QBM_PSessionContextSet 'XUser', @XUser_R return (@AnzahlGesamt) end 19