Back to OIM Explorer

dbo.DPR_PAttachedDataStoreCleanUp

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_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL84 lines
1CREATE PROCEDURE DPR_PAttachedDataStoreCleanUp2AS3BEGIN4  DECLARE @cmd nvarchar(max)5  DECLARE @TableName varchar(30)6  DECLARE @DebugSwitch int = 07  DECLARE @ElementBuffer QBM_YCursorBuffer8  DECLARE @ElementCount int9  DECLARE @ElementIndex int10  SET XACT_ABORT OFF11  BEGIN TRY12    DELETE DPRAttachedDataStore13    WHERE14      OwnerObject IS NULL15    INSERT INTO @ElementBuffer(Ident1)16    SELECT17      DISTINCT dbo.QBM_FCVObjectkeyToElement('TableName',18      s.OwnerObject)19    FROM DPRAttachedDataStore s20    WHERE21      s.OwnerObject LIKE '<Key><T>%'22    SELECT @ElementCount = @@ROWCOUNT23    SELECT @ElementIndex = 124    WHILE @ElementIndex <= @ElementCount25    BEGIN26      SELECT TOP 1 @TableName = bu.Ident127      FROM @ElementBuffer bu28      WHERE29        bu.ElementIndex = @ElementIndex30      SELECT31        @cmd = '32	delete DPRAttachedDataStore33		from DPRAttachedDataStore s left outer join ' + @TableName + '  e on s.OwnerObject = e.XObjectKey34		where s.OwnerObject like ''<Key><T>'35        + @TableName + '</T>%''36		 and e.XObjectKey is null'37      IF @DebugSwitch > 038      BEGIN39        print @cmd40      END41      EXEC sp_executeSQL @cmd42      SELECT @ElementIndex += 143    END44    DELETE DPRAttachedDataStore45    FROM DPRAttachedDataStore ds46    JOIN(47    SELECT48      a.UID_DPRAttachedDataStore,49      substring(a.SourceInfo, 13, 38) AS UID_DPRSystemMappingRule50    FROM DPRAttachedDataStore a51    WHERE52      a.SourceInfo LIKE 'MembersRule:%') AS x53      ON ds.UID_DPRAttachedDataStore = x.UID_DPRAttachedDataStore54    LEFT55    OUTER56    JOIN DPRSystemMappingRule mr57      ON x.UID_DPRSystemMappingRule = mr.UID_DPRSystemMappingRule58    WHERE59      mr.UID_DPRSystemMappingRule IS NULL60    DELETE DPRAttachedDataStore61    FROM DPRAttachedDataStore s62    WHERE63      NOT EXISTS(64    SELECT TOP 1 165    FROM(66    SELECT67      'Schema[' + s.SystemId + '].Type[' + st.Name + '].Property[' + sp.Name + ']' AS OwnerProperty68    FROM DPRSchema s69    JOIN DPRSchemaType st70      ON s.UID_DPRSchema = st.UID_DPRSchema71    JOIN DPRSchemaProperty sp72      ON st.UID_DPRSchemaType = sp.UID_DPRSchemaType) AS x73    WHERE74      x.OwnerProperty = s.OwnerProperty)75  END TRY76  BEGIN CATCH77    EXEC QBM_PSessionErrorAdd DEFAULT78    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()79    RAISERROR(@Rethrow,80    18,81    1)82      WITH NOWAIT83  END CATCH84END
Open raw exported source
SQL ยท Raw18 lines
1   create   procedure DPR_PAttachedDataStoreCleanUp as begin declare @cmd nvarchar(max) declare @TableName varchar(30) declare @DebugSwitch int 2= 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int SET XACT_ABORT OFF BEGIN TRY delete DPRAttachedDataStore3 where OwnerObject is null  insert into @ElementBuffer (Ident1) select distinct dbo.QBM_FCVObjectkeyToElement('TableName', s.OwnerObject ) from DPRAttachedDataStore4 s where s.OwnerObject like '<Key><T>%' select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top5 1 @TableName = bu.Ident1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @cmd = '6	delete DPRAttachedDataStore7		from DPRAttachedDataStore s left outer join '8 + @TableName + '  e on s.OwnerObject = e.XObjectKey9		where s.OwnerObject like ''<Key><T>' + @TableName + '</T>%''10		 and e.XObjectKey is null' if @DebugSwitch11 > 0 begin print @cmd end exec sp_executeSQL @cmd select @ElementIndex += 1 end   delete DPRAttachedDataStore  from DPRAttachedDataStore ds join ( select12 a.UID_DPRAttachedDataStore, substring(a.SourceInfo , 13, 38) as UID_DPRSystemMappingRule from DPRAttachedDataStore a where a.SourceInfo like 'MembersRule:%'13 ) as x on ds.UID_DPRAttachedDataStore = x.UID_DPRAttachedDataStore left outer join DPRSystemMappingRule mr on x.UID_DPRSystemMappingRule = mr.UID_DPRSystemMappingRule14 where mr.UID_DPRSystemMappingRule is null  delete DPRAttachedDataStore from DPRAttachedDataStore s where not exists (select top 1 1 from ( select 'Schema['15 + s.SystemId + '].Type[' + st.Name + '].Property[' + sp.Name + ']' as OwnerProperty from DPRSchema s join DPRSchemaType st on s.UID_DPRSchema = st.UID_DPRSchema16 join DPRSchemaProperty sp on st.UID_DPRSchemaType = sp.UID_DPRSchemaType ) as x where x.OwnerProperty = s.OwnerProperty ) END TRY BEGIN CATCH exec QBM_PSessionErrorAdd17 default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH end 18