How to Remove Incomplete Activity Instances

How to Remove Incomplete Activity I...

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 references, then there will be data in the bam_ActivityName_ActiveRelationships table.

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

To create the stored procedure, copy the script and execute it against the BAM Primary Import database by using SQL Server Management. The script will generate a stored procedure named RemoveDanglingInstances in the database.

To create the RemoveDanglingInstances stored procedure

Click Start, click All Programs, click Microsoft SQL Server 2008 (if you are using SQL Server 2005, click Microsoft SQL Server 2005), and then click SQL Server Management Studio.
In the Connect to Server dialog box, select the SQL server and the appropriate authentication method, and then click Connect.
Expand the server name, expand Databases, and then select the BAM Primary Import database.
Click New Query.
Copy the stored procedure creation script and paste it into the right pane.
Click Execute 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

Click Start, click All Programs, click Microsoft SQL Server 2008 (if you are using SQL Server 2005, click Microsoft SQL Server 2005), and then click SQL Server Management Studio.
In the Connect to Server dialog box, select the SQL server and the appropriate authentication method, and then click Connect.
Expand the server name, expand Databases, and then select the BAM Primary Import database.
Click New Query.
In the right pane, 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'.
Click Execute to run the script.

RemoveDanglingInstances Usage Examples

The stored procedure can receive four 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 to and older, only 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. Important If the tables already exist, the stored procedure assumes that their schemas match the ones that would be used if they were created. If a schema does not match, the stored procedure will fail to insert the records and the remove 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 exists 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

Other Resources

Managing BAM Databases