How to reset WorkflowGen runtime content

Administration Database
1

Question

How do I reinitialize WorkflowGen while keeping my web forms, web services and users? I would like the request count and the history of past requests to reinitialize.

Solution

These three SQL queries will remove all existing requests and reset the counters back to 0 while keeping all process definitions and users.

You’ll also need to manually delete all requests file data in \wfgen\App_Data\Files\DataSet\runtime as indicated below.

Delete all requests and actions

DELETE FROM WFPROCESS_INST;

Delete all request and action data

For all the data files stored in the file system, you’ll need to manually delete the \wfgen\App_Data\Files\DataSet\runtime folder.

DELETE FROM WFDATASET WHERE RUNTIME = 'Y';

Reset all processes’ relative IDs and request IDs

UPDATE WFCOUNTER SET COUNTER_VALUE = 0 WHERE ID_COUNTER <> 'WFPROCESS.ID_PROCESS' AND ID_COUNTER <> 'WFDATASET.ID_DATASET';

Removing users and groups

If you want to remove all users and groups (except the wfgen_admin account), you’ll have to execute the following SQL queries as well:

Delete all regular users’ information (not admin)

  • USERS_PREFS and USERS_VIEWS are preserved for administrators.

  • Delegations (WFDELEGATE) are preserved for admins if the delegation are made between administrator accounts

DELETE FROM USERS WHERE ID_USER NOT IN (SELECT ID_USER FROM USERS_PROFILE WHERE ID_PROFILE = 1);

Delete all groups that have no members

DELETE FROM GROUPS WHERE ID_GROUP NOT IN (SELECT ID_GROUP FROM USERS_GROUPS, USERS WHERE USERS_GROUPS.ID_USER = USERS.ID_USER);

Delete all directories that have no users and groups

DELETE FROM DIRECTORY WHERE ID_DIRECTORY NOT IN (SELECT ID_DIRECTORY FROM USERS) AND ID_DIRECTORY NOT IN (SELECT ID_DIRECTORY FROM GROUPS);

Important notes

  • After deleting most of the users, groups and directories, it’s possible that some participants have no associations at all.

  • Use the following query to get a list of participants without associations:

    SELECT DISTINCT ID_PARTICIPANT, NAME FROM WFPARTICIPANT WHERE ID_PARTICIPANT NOT IN (SELECT ID_PARTICIPANT FROM WFPARTICIPANT_MAPTO);

  • The following Transact-SQL block will create a new association for those participants with the first administrator account (compatible with MS SQL Server only).

    DECLARE @ID_PARTICIPANT_TEMP int;  
DECLARE PARTICIPANT_CURSOR CURSOR FOR SELECT DISTINCT ID_PARTICIPANT FROM WFPARTICIPANT WHERE ID_PARTICIPANT NOT IN (SELECT ID_PARTICIPANT FROM WFPARTICIPANT_MAPTO);  
OPEN PARTICIPANT_CURSOR;  
FETCH NEXT FROM PARTICIPANT_CURSOR INTO @ID_PARTICIPANT_TEMP;  
WHILE @@FETCH_STATUS = 0  
    BEGIN  
        INSERT INTO WFPARTICIPANT_MAPTO (ID_PARTICIPANT, NUM_MAPTO, MAPTO, ID_USER, COORDINATOR) VALUES (@ID_PARTICIPANT_TEMP, 1, 'USERS', (SELECT MIN(ID_USER) FROM USERS), 'N');  
        FETCH NEXT FROM PARTICIPANT_CURSOR;  
    END;  
CLOSE PARTICIPANT_CURSOR;  
DEALLOCATE PARTICIPANT_CURSOR;