How to Remove Incomplete Activity Instances

When a BAM definition file is deployed, five tables are created in the BAM Primary Import database for each activity defined in the definition file. These tables are:

  • bam_ActivityName_Active

  • bam_ActivityName_Completed

  • bam_ActivityName_ActiveRelationships

  • bam_ActivityName_CompletedRelationships

  • bam_ActivityName_Continuations

Where ActivityName is the name of the activity that the user has defined.

During normal execution, incomplete data remains in the bam_ActivityName_Active table. If the data has relations and reference, then there will be data in the bam_ActivityName_ActiveRelationships tables.

During the tracking of activities that use continuations it is possible that there will be instances in which an activity is left in an incomplete state in the BAM databases. You can use the SQL script (the stored procedures creation script) at the end of this topic to create a stored procedure that you can use to purge the incomplete records.

To create the stored procedure, copy the script and execute it against the BAM Primary Import database by using Management Studio (SQL Server 2005) or Query Analyzer (SQL Server 2000). The script will generate a stored procedure named RemoveDanglingInstances in the database.

To create the RemoveDanglingInstances stored procedure using SQL Server 2000

  1. Click Start, click Programs, click Microsoft SQL Server, and then click Query Analyzer.

  2. In the Connect to SQL Server dialog box, select the SQL server and the appropriate authentication method, and then click OK.

  3. In the Change database drop-down list, select the BAM Primary Import database.

  4. Copy the stored procedure creation script and paste it into the Query Window.

  5. Click the Execute Query icon on the toolbar to run the script. The resulting stored procedure can be viewed in the list of stored procedures as dbo.RemoveDanglingInstances.
To create the RemoveDanglingInstances stored procedure using SQL Server 2005

  1. Click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. In the Connect to SQL Server dialog box, select the SQL server and the appropriate authentication method, and then click Connect.

  3. In the Available databases drop-down list, select the BAM Primary Import database.

  4. Click the New Query icon on the toolbar.

  5. Copy the stored procedure creation script and paste it into the Query Window.

  6. Click the Execute Query icon on the toolbar to run the script. The resulting stored procedure can be viewed in the list of stored procedures as dbo.RemoveDanglingInstances
To remove incomplete activity instances using SQL Server 2000

  1. Click Start, click Programs, click Microsoft SQL Server, and then click Query Analyzer.

  2. In the Connect to SQL Server dialog box, select the SQL server and the appropriate authentication method, and then click OK.

  3. In the Change database drop-down list, select the BAM Primary Import database.

  4. In the Query Window, type exec RemoveDanglingInstances and the appropriate parameters for the remove operation you are performing. For example, to remove all incomplete instances of the Purchase Order acivity, type exec RemoveDanglingInstances @ActivityName = 'PurchaseOrder'.

  5. Click the Execute Query icon on the toolbar.
To remove incomplete activity instances using SQL Server 2005

  1. Click Start, click Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.

  2. In the Connect to SQL Server dialog box, select the SQL server and the appropriate authentication method, and then click Connect.

  3. In the Available databases drop-down list, select the BAM Primary Import database.

  4. Click the New Query icon on the toolbar.

  5. In the Query Window, type exec RemoveDanglingInstances and the appropriate parameters for the remove operation you are performing. For example, to remove all incomplete instances of the Purchase Order activity, type exec RemoveDanglingInstances @ActivityName = 'PurchaseOrder'.

  6. Click the Execute Query icon on the tool bar.
RemoveDanglingInstances Usage Examples

The stored procedure can receive 4 parameters:

Parameter Description

@ActivityName nvarchar(128)

Specifies the name of the incomplete activity instance to remove.

@ActivityId nvarchar(128)

(Optional) Specifies that the stored procedure remove only the dangling instance with the specified instance identifier.

@DateThreshold datetime

(Optional) Specifies that all the active instances in the active table that are older (not equal and older, just older) than the given date are removed.

@NewTableExtension nvarchar(30)

(Optional) Specifies that the stored procedure creates three new tables by concatenating the supplied extension to the existing activity tables.

The resulting tables will be:

bam_ActivityName_Active_<Extension>

bam_ActivityName_ActiveRelationships_<Extension>

bam_ActivityName_Continuations_<Extension>

The incomplete instances are moved to the new tables rather than being purged from the database.

If the tables already exist, the stored procedure reuses them; otherwise they are created.

Aa560758.Important(en-us,BTS.20).gifImportant
If the tables already exist, the stored procedure assumes that their schema matches the ones that would be used if they were created. If the schema does not match, the stored procedure will fail to insert the records and the removed operation will fail.

exec RemoveDanglingInstances @ActivityName = 'PurchaseOrder'

Removes all active instances of the 'PurchaseOrder' activity in the active, active relationships, and continuations tables.

exec RemoveDanglingInstances @ActivityName = 'PurchaseOrder', @ActivityId = 'PO220567'

Removes only the activity instance that has an Activity ID of 'PO220567' from the active, active relationships, and continuations tables for the 'PurchaseOrder' activity.

exec RemoveDanglingInstances @ActivityName = 'PurchaseOrder', @DateThreshold='2005-02-02 19:27:03:533'

Removes all the activity instances that have a LastModified time that is older than Feb 2nd, 2005 7:27:03.533 PM from the active, active relationships, and continuations tables for the 'PurchaseOrder' activity.

exec RemoveDanglingInstances @ActivityName = 'PurchaseOrder', @ActivityId = 'PO220567', @DateThreshold='2005-02-02 19:27:03:533'

Removes the activity instance whose activity id is PO220567 only if its LastModified column is older than Feb 2nd, 2005 7:27:03.533 PM.

exec RemoveDanglingInstances @ActivityName = 'PurchaseOrder', @DateThreshold='2005-02-02 19:27:03:533', @NewTableExtension=N'Dangling'

Creates the following tables in the database:

bam_PurchaseOrder_Active_Dangling

bam_PurchaseOrder_ActiveRelationships_Dangling

bam_PurchaseOrder_Continuations_Dangling

The stored procedure copies all incomplete activity instances that are older than Feb 2nd, 2005 7:27:03.533 PM from the active, active relationships, and continuations tables for the 'PurchaseOrder' activity, and inserts them into the newly created tables. The copied activity instances are then removed from the active, active relationships, and continuations tables.
Stored Procedure Creation Script
EXEC sp_stored_procedures @sp_name = 'RemoveDanglingInstances'
IF @@ROWCOUNT > 0 DROP PROCEDURE RemoveDanglingInstances
GO

CREATE PROCEDURE RemoveDanglingInstances
    @ActivityName nvarchar(128),
    @ActivityId nvarchar(128) = NULL,
    @DateThreshold datetime = NULL,
    @NewTableExtension nvarchar(30) = NULL
AS
    DECLARE @QueryString nvarchar(4000)
    DECLARE @ActiveTableName sysname
    DECLARE @ActiveRelationshipsTableName sysname
    DECLARE @ContinuationsTableName sysname
    DECLARE @DanglingActiveTableName sysname
    DECLARE @DanglingActiveRelationshipsTableName sysname
    DECLARE @DanglingContinuationsTableName sysname

    SET @ActiveTableName = 'bam_' + @ActivityName + '_Active'
    SET @ActiveRelationshipsTableName = 'bam_' + @ActivityName + '_ActiveRelationships'
    SET @ContinuationsTableName = 'bam_' + @ActivityName + '_Continuations'

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRAN

    DECLARE @LockActivity nvarchar(128)
    SELECT @LockActivity = ActivityName
    FROM bam_Metadata_Activities WITH (XLOCK)
    WHERE ActivityName = @ActivityName

    EXEC sp_tables @table_name = #DanglingActivities
    IF @@ROWCOUNT > 0 DROP TABLE #DanglingActivities

    CREATE TABLE #DanglingActivities(ActivityID nvarchar(128) PRIMARY KEY)

    SET @QueryString = N'INSERT INTO #DanglingActivities (ActivityID) SELECT ActivityID FROM [bam_' + @ActivityName + '_Active]'

    IF (@DateThreshold is not NULL) OR (@ActivityId is not NULL)
    BEGIN
        SET @QueryString = @QueryString + ' WHERE'
    END

    IF (@DateThreshold is not NULL)
    BEGIN
        SET @QueryString = @QueryString + ' LastModified < N''' + CONVERT(nvarchar(50), @DateThreshold, 109) + ''''
        IF (@ActivityId is not NULL)
        BEGIN
            SET @QueryString = @QueryString + ' AND'
        END
    END

    IF (@ActivityId is not NULL)
    BEGIN
        SET @QueryString = @QueryString + ' ActivityID = N''' + @ActivityId + ''''
    END

    EXEC sp_executesql @QueryString
    SELECT * FROM #DanglingActivities

    SET @QueryString = N''

    -- If the user gave a table extension, the dangling instances will be inserted
    -- into that table. 
    IF (isnull(@NewTableExtension, '') <> '')
    BEGIN
        SET @DanglingActiveTableName = @ActiveTableName + '_' + @NewTableExtension
        SET @DanglingActiveRelationshipsTableName = @ActiveRelationshipsTableName + '_' + @NewTableExtension
        SET @DanglingContinuationsTableName = @ContinuationsTableName + '_' + @NewTableExtension

        -- If the table for the dangling instances exist then insert into it
        -- If the table does not exist, then create the dangling instances table
        -- and then insert into it. SELECT INTO will do that.
        EXEC sp_tables @table_name = @DanglingActiveTableName
        IF @@ROWCOUNT > 0
        BEGIN
            SET @QueryString = N'INSERT INTO ' + '[' + @DanglingActiveTableName + '] SELECT active.* FROM [' + @ActiveTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
            EXEC sp_executesql @QueryString
        END
        ELSE
        BEGIN
            SET @QueryString = N'SELECT active.* INTO [' + @DanglingActiveTableName + '] FROM [' + @ActiveTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
            EXEC sp_executesql @QueryString
        END

        -- Now do what you did for the Active Instances table for the 
        -- ActiveRelationships table
        EXEC sp_tables @table_name = @DanglingActiveRelationshipsTableName
        IF @@ROWCOUNT > 0
        BEGIN
            SET @QueryString = N'INSERT INTO ' + '[' + @DanglingActiveRelationshipsTableName + '] SELECT active.* FROM [' + @ActiveRelationshipsTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
            EXEC sp_executesql @QueryString
        END
        ELSE
        BEGIN
            SET @QueryString = N'SELECT active.* INTO [' + @DanglingActiveRelationshipsTableName + '] FROM [' + @ActiveRelationshipsTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID'
            EXEC sp_executesql @QueryString
        END

        -- And finally for the continuations table
        EXEC sp_tables @table_name = @DanglingContinuationsTableName
        IF @@ROWCOUNT > 0
        BEGIN
            SET @QueryString = N'INSERT INTO ' + '[' + @DanglingContinuationsTableName + '] SELECT active.* FROM [' + @ContinuationsTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID'
            EXEC sp_executesql @QueryString
        END
        ELSE
        BEGIN
            SET @QueryString = N'SELECT active.* INTO [' + @DanglingContinuationsTableName + '] FROM [' + @ContinuationsTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID'
            EXEC sp_executesql @QueryString
        END
    END

    -- Remove the dangling instances from the Active Instances Table
    SET @QueryString = 'DELETE FROM [' + @ActiveTableName + '] FROM [' + @ActiveTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID '
    EXEC sp_executesql @QueryString

    SET @QueryString = 'DELETE FROM [' + @ActiveRelationshipsTableName + '] FROM [' + @ActiveRelationshipsTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ActivityID = dangling.ActivityID '
    EXEC sp_executesql @QueryString

    SET @QueryString = 'DELETE FROM [' + @ContinuationsTableName + '] FROM [' + @ContinuationsTableName + '] active INNER JOIN #DanglingActivities dangling ON active.ParentActivityID = dangling.ActivityID '
    EXEC sp_executesql @QueryString

    DROP TABLE #DanglingActivities

    COMMIT TRAN    
GO
Next Steps
See Also

Other Resources

Managing BAM Databases

Tags :


Page view tracker