dbo.QBM_ZPrePropTable

1CREATE PROCEDURE QBM_ZPrePropTable(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @GenProcID varchar(38)7  DECLARE @TableName varchar(38)8  DECLARE @CountItems int9  DECLARE @SQLcmd nvarchar(max)10  DECLARE @subject nvarchar(1000)11  DECLARE @rowcnt int12  DECLARE @DebugLevel char(1) = 'W'13  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')14  DECLARE @ElementBuffer QBM_YCursorBuffer15  DECLARE @ElementCount int16  DECLARE @ElementIndex int17  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent18  DECLARE @XUser nvarchar(64) = object_name(@@procid)19  DECLARE @Xdate datetime = getutcdate()20  BEGIN TRY21    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,22    UID_Parameter,23    UID_SubParameter,24    GenProcID)25    SELECT26      UID_DialogDBQueue,27      UID_Parameter,28      UID_SubParameter,29      GenProcID30    FROM QBMDBQueueCurrent cu31      WITH(readpast)32    WHERE33      cu.SlotNumber = @SlotNumber34    IF @@rowcount = 035    BEGIN36      GOTO EndLabel37    END38    SELECT @GenProcID = max(GenProcID)39    FROM @DBQueueCurrent40    WHERE41      GenProcID > ' '42    IF isnull(@Genprocid,43    '') = ''44    BEGIN45      SELECT @GenProcID = newid()46    END47    DECLARE @PrePropHelper QBM_YPrepropHelper48    INSERT INTO @PrePropHelper(uid_parameter,49    IsDeactivated,50    IsDeactivatedOld,51    GenProcID,52    PreProcessorCondition)53    SELECT54      x.uid_parameter,55      0 + isnull(b.isDeactivatedByPreProcessor,56      0),57      t.isDeactivatedByPreProcessor,58      x.GenProcID,59      isnull(t.PreProcessorCondition,60      '')61    FROM @DBQueueCurrent x62    JOIN dialogtable t63      ON x.uid_parameter = t.TableName64    LEFT65    OUTER66    JOIN dialogtable b67      ON t.UID_DialogTableBase = b.UID_DialogTable68    UPDATE @PrePropHelper69    SET IsDeactivated = dbo.QBM_FGIPrepropConditionDeactiv(h.PreProcessorCondition)70    FROM @PrePropHelper h71    WHERE72      h.PreProcessorCondition > ' '73    IF EXISTS(74      SELECT TOP 1 175      FROM DialogTable,76    @PrePropHelper x77      WHERE78        DialogTable.TableName = x.uid_parameter AND isnull(DialogTable.IsDeactivatedByPreProcessor, 0) <> x.IsDeactivated AND dialogtable.TableName79    NOT IN(80    SELECT childtable81    FROM QBM_VQBMRelation82    WHERE83      isMNRelation = 1))84    BEGIN85      EXEC QBM_PDBQueueInsert_WaitForComp 'Table',86        @GenProcID87    END88    EXEC QBM_PSessionContextSet 'GenProcID',89      @GenProcID90    UPDATE DialogTable91    SET IsDeactivatedByPreProcessor = x.isDeactivated,92    XDateUpdated = @Xdate,93    XUserUpdated = @XUser94    FROM DialogTable r95    JOIN @PrePropHelper x96      ON r.TableName = x.uid_parameter97    WHERE98      (r.IsDeactivatedByPreProcessor <> x.IsDeactivated OR x.IsDeactivated = 1)99    SELECT @CountItems = @@rowcount100    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw101    INSERT INTO @DBQueueElements_01(object,102    subobject,103    genprocid)104    SELECT105      x.uid,106      NULL,107      @GenProcID108    FROM(109    SELECT110      DISTINCT t.TableName AS UID,111      x.GenProcID112    FROM Dialogtable t113    JOIN DialogTable b114      ON t.UID_DialogTableBase = b.UID_DialogTable115    JOIN @PrePropHelper x116      ON b.TableName = x.uid_parameter AND(x.IsDeactivated <>x.IsDeactivatedOld OR x.IsDeactivated = 1)) AS x117    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTable',118      @DBQueueElements_01119    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw120    INSERT INTO @DBQueueElements_02(object,121    subobject,122    genprocid)123    SELECT124      x.UID,125      NULL,126      x.GenProcID127    FROM(128    SELECT129      DISTINCT c.UID_DialogColumn AS UID,130      x.GenProcID131    FROM DialogColumn c132    JOIN DialogTable t133      ON c.UID_DialogTable = t.UID_DialogTable134    JOIN @PrePropHelper x135      ON t.TableName = x.uid_parameter AND(x.IsDeactivated <>x.IsDeactivatedOld OR c.IsDeactivatedByPreprocessor <> x.IsDeactivated OR c.IsPKMember136    = 1)) AS x137    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumn',138      @DBQueueElements_02139    DECLARE @DBQueueElements_03 QBM_YDBQueueRaw140    INSERT INTO @DBQueueElements_03(object,141    subobject,142    genprocid)143    SELECT144      x.UID,145      NULL,146      x.GenProcID147    FROM(148    SELECT149      DISTINCT r.UID_ChildColumn AS uid,150      x.GenProcID151    FROM QBM_VQBMRelation r152    JOIN @PrePropHelper x153      ON r.ParentTable = x.uid_parameter AND r.ChildTable <> r.ParentTable154    JOIN DialogColumn c155      ON r.UID_ChildColumn = c.UID_DialogColumn156    WHERE157      (x.IsDeactivated <> x.IsDeactivatedOld OR c.IsDeactivatedByPreprocessor <> x.IsDeactivated)) AS x158    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropColumnFK',159      @DBQueueElements_03160    DECLARE @DBQueueElements_04 QBM_YDBQueueRaw161    INSERT INTO @DBQueueElements_04(object,162    subobject,163    genprocid)164    SELECT165      x.uid,166      NULL,167      @GenProcID168    FROM(169    SELECT170      DISTINCT y.UID_QBMTree AS UID,171      x.GenProcID172    FROM(173    SELECT174      t.uid_QBMTree, ta.TableName175    FROM QBMTree t176    JOIN DialogTable ta177      ON t.UID_DialogTable = ta.UID_DialogTable178    WHERE179      ta.TableName > ' ') AS y180    JOIN @PrePropHelper x181      ON y.TableName = x.uid_parameter182    JOIN QBMTree t183      ON t.uid_QBMTree = y.uid_QBMTree184    WHERE185      (x.IsDeactivated <>x.IsDeactivatedOld OR t.IsDeactivatedByPreprocessor <> x.IsDeactivated)) AS x186    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTree',187      @DBQueueElements_04188    DECLARE @DBQueueElements_04Result QBM_YDBQueueRaw189    INSERT INTO @DBQueueElements_04Result(object,190    subobject,191    genprocid)192    SELECT193      x.uid,194      NULL,195      @GenProcID196    FROM(197    SELECT198      DISTINCT y.UID_QBMTreeResult AS UID,199      x.GenProcID200    FROM(201    SELECT202      t.uid_QBMTreeResult, ta.TableName203    FROM QBMTreeResult t204    JOIN Dialogobject o205      ON t.uid_DialogObject = o.uid_DialogObject206    JOIN DialogTable ta207      ON o.UID_DialogTable = ta.UID_DialogTable) AS y208    JOIN @PrePropHelper x209      ON y.TableName = x.uid_parameter210    JOIN QBMTreeResult t211      ON t.uid_QBMTreeResult = y.uid_QBMTreeResult212    WHERE213      (x.IsDeactivated <>x.IsDeactivatedOld OR t.IsDeactivatedByPreprocessor <> x.IsDeactivated)) AS x214    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropTreeResult',215      @DBQueueElements_04Result216    DECLARE @DBQueueElements_05 QBM_YDBQueueRaw217    INSERT INTO @DBQueueElements_05(object,218    subobject,219    genprocid)220    SELECT221      x.uid,222      NULL,223      @GenProcID224    FROM(225    SELECT226      DISTINCT c.UID_Jobchain AS UID,227      x.GenProcID228    FROM Jobchain c229    JOIN DialogTable ta230      ON c.UID_DialogTable = ta.UID_DialogTable231    JOIN @PrePropHelper x232      ON ta.TableName = x.uid_parameter AND(x.IsDeactivated <>x.IsDeactivatedOld OR c.IsDeactivatedByPreprocessor <> x.IsDeactivated)) AS x233    EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropJobChain',234      @DBQueueElements_05235    UPDATE DialogColumn236    SET IsDeactivatedByPreProcessor = 1,237    XDateUpdated = @Xdate,238    XUserUpdated = @XUser239    FROM DialogColumn c240    JOIN DialogTable t241      ON c.UID_DialogTable = t.UID_DialogTable242    JOIN @PrePropHelper x243      ON x.UID_Parameter = t.TableName244    WHERE245      x.IsDeactivated = 1 AND c.IsDeactivatedByPreProcessor = 0246    IF @CountItems > 0247    BEGIN248      DECLARE @DBQueueElements_06 QBM_YDBQueueRaw249      INSERT INTO @DBQueueElements_06(object,250      subobject,251      genprocid)252      SELECT253        x.UID,254        NULL,255        x.GenProcID256      FROM(257      SELECT258        t.UID_DialogTable AS UID,259        x.GenProcID260      FROM Dialogtable t261      JOIN DialogTable b262        ON t.UID_DialogTableBase = b.UID_DialogTable263      JOIN @PrePropHelper x264        ON b.TableName = x.uid_parameter AND(x.IsDeactivated <>x.IsDeactivatedOld OR x.IsDeactivated = 1)265      UNION266      SELECT267        p.UID_DialogTable AS UID,268        x.GenProcID269      FROM Dialogtable t270      JOIN dialogtable p271        ON t.UID_DialogTableUnion = p.UID_DialogTable272      JOIN @PrePropHelper x273        ON t.TableName = x.uid_parameter AND(x.IsDeactivated <>x.IsDeactivatedOld OR x.IsDeactivated = 1)274      WHERE275        t.UID_DialogTableUnion > ' ') AS x276      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonRebuildView',277        @DBQueueElements_06278      SELECT @SQLcmd = N ''279      INSERT INTO @ElementBuffer(Ident1,280      Int1,281      UID1)282      SELECT283        t.TableName,284        i.rowcnt,285        x.GenProcID286      FROM dialogtable t287        WITH(readpast)288      JOIN @PrePropHelper x289        ON t.TableName = x.uid_parameter AND t.isdeactivatedbypreprocessor = 1 AND isnull(x.IsDeactivated,290      0) <> isnull(x.IsDeactivatedOld,291      0)292      JOIN sys.objects o293        WITH(readpast)294        ON t.TableName = o.name295      JOIN(296      SELECT297        object_id,298        avg(rows) AS rowcnt299      FROM(300      SELECT301        object_id, avg(rows) AS rows302      FROM sys.partitions303        WITH(readpast)304      GROUP BY object_id, index_id) AS x305      GROUP BY object_id) AS i306        ON o.object_id = i.object_id AND i.rowcnt > 0307      ORDER BY t.TableName308      SELECT @ElementCount = @@ROWCOUNT309      SELECT @ElementIndex = 1310      WHILE @ElementIndex <= @ElementCount311      BEGIN312        SELECT313          TOP 1 @TableName = bu.Ident1,314          @rowcnt = bu.Int1,315          @GenProcID = bu.UID1316        FROM @ElementBuffer bu317        WHERE318          bu.ElementIndex = @ElementIndex319        SELECT320          @SQLcmd = N 'TableName' + nchar(9) + N 'Rowcount' + nchar(13) +nchar(10) +replicate(N '-',321          40)322        SELECT323          @SQLcmd = @SQLcmd + nchar(13) + nchar(10) + @TableName + nchar(9) + cast(@rowcnt AS nvarchar(16))324        SELECT325          @subject = N 'There are deactivated tables with data! (' + isnull(@@servername,326          N '') + N '.' + isnull(db_name(),327          N '') + N ')' + @TableName + nchar(9) + cast(@rowcnt AS nvarchar(16))328        EXEC QBM_PJournal @subject,329          @@procid,330        'D',331          @DebugLevel332        IF dbo.QBM_FGIConfigparmValue('Common\MailNotification') > ' '333        BEGIN334          EXEC QBM_PJobCreate_SendMail @subject,335            @Message = @SQLcmd,336            @GenProcID = @GenProcID,337            @checkForExisting = 1,338            @AdditionalObjectKeysAffected = DEFAULT339        END340        SELECT @ElementIndex += 1341      END342      DECLARE @DBQueueElements_07 QBM_YDBQueueRaw343      INSERT INTO @DBQueueElements_07(object,344      subobject,345      genprocid)346      SELECT347        x.uid,348        NULL,349        @GenProcID350      FROM(351      SELECT352        DISTINCT o.UID_DialogObject AS UID,353        x.GenProcID354      FROM DialogObject o355      JOIN DialogTable t356        ON o.UID_DialogTable = t.UID_DialogTable357      JOIN @PrePropHelper x358        ON t.TableName = x.uid_parameter AND x.IsDeactivated <>x.IsDeactivatedOld) AS x359      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonPrepropObject',360        @DBQueueElements_07361      SELECT @GenProcID = max(GenProcID)362      FROM @DBQueueCurrent363      WHERE364        GenProcID > ' '365      IF isnull(@GenProcID,366      '') = ''367      BEGIN368        SELECT @GenProcID = newid()369      END370      DECLARE @DBQueueElements_08 QBM_YDBQueueRaw371      INSERT INTO @DBQueueElements_08(object,372      subobject,373      genprocid)374      SELECT375        x.UID,376        NULL,377        x.GenProcID378      FROM(379      SELECT380        x.uid_parameter AS UID,381        x.GenProcID382      FROM @PrePropHelper x383      WHERE384        x.IsDeactivated <>x.IsDeactivatedOld) AS x385      EXEC QBM_PDBQueueInsert_Bulk 'QBM-K-CommonMakeWatchtrigger',386        @DBQueueElements_08387    END388  END TRY389  BEGIN CATCH390    EXEC QBM_PSessionErrorAdd DEFAULT391    RAISERROR('',392    18,393    1)394      WITH NOWAIT395  END CATCH396  endLabel:397  EXEC QBM_PSessionContextSet 'GenProcID',398    @GenProcID_R399  RETURN400END