Microsoft Access Workflow Designer: Understanding Schema Changes and How to Remove Them
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.Adam Ulrich
Microsoft Corporation
September 1, 1999
Summary: This paper identifies the changes made to a database when Microsoft® Access Workflow Designer for SQL Server™ features are added. In addition, this paper explains how to remove the features successfully. (8 printed pages)
Introduction
Microsoft Office Developer includes a new component, Microsoft Access Workflow Designer for Microsoft SQL Server, which contains a number of tools designed to build integrated Web- and SQL Server-based solutions that automate business processes easily for teams of knowledge workers.
When a SQL Server database is registered as an Access Workflow Designer team solution, the existing database is extended with many valuable additions so the solution can take advantage of such features as workflow, offline replication, and security. This paper identifies those additions so that, if necessary, you can remove the schema extensions and return your database to a clean state.
The amount of work required to return your database to its original schema depends on the state of your database.
In the simplest scenario, if you created a backup of your database before registering it as an Access Workflow Designer solution, you can revert to the backup database to remove the Access Workflow Designer extensions.
If additional data is added to your database, however, reverting to a backup is not an ideal situation. In this case, it is recommended that you use SQL data transformation services (DTS) to transfer the objects and data in your database to another database, without transferring the solution extensions.
If the previous two approaches are not feasible, the remaining alternative is the selective removal of all Microsoft Office Developer additions to the database. This document describes the objects and changes made by the Access Workflow Designer and, subsequently, how to use either DTS or SQL Server Enterprise Manager to transfer the objects you want to preserve or to remove the objects you no longer require.
| For information about: | See: |
|---|---|
| The database objects added to a database when you register the database as a team solution and add workflow features. | Components Created or Modified by Access Workflow Designer |
| How to remove database objects from your database to remove the Access Workflow Designer features. | Removing Access Workflow Designer Components |
| Where to find additional information about the Access Workflow Designer. | For More Information |
Components Created or Modified by Access Workflow Designer
To develop and administer team solutions successfully using the Access Workflow Designer, you should be familiar with all the objects that are added to or modified in your solution database. Objects are created or modified during the initial setup and database registration of the Access Workflow Designer components, as well as when features such as workflow, offline replication, and security are added to your solution.
Installing Access Workflow Designer
The Access Workflow Designer Setup program that installs the server components adds several extended stored procedures (all prefixed with ‘xp_mod’) to the Master database in SQL Server. Setup creates a new database, modSystem, where solution and template information and additional stored procedures are stored. This combination of stored procedures and the modSystem database provide the infrastructure for the core functionality of team solutions.
In addition, during Setup, a local Microsoft Windows NT® Group, modAppOwners, is created on the server. This group (<Server Name>\modAppOwners) is mapped to SQL Server as a new role—modAppOwners. The modAppOwners group provides the required permissions for a user to create and manage team solutions on the server.
Registering Your Database
When you register a database in Access Workflow Designer, 31 stored procedures, 7 tables, and 3 views are added to the database. None of these modify any existing objects in your database.
When a database is registered, information about that database is added to the modSystem database.
The following objects are added to a solution database during registration:
| Tables | modUserList modColumns modObjects modObjectTypes modPermissions modProperties modUserRoles |
| Views | modEnumColumnPermissionsView modGetColListPermissionsView modUserList |
| Stored Procedures | modAddTable modBuildHelperInsert modCheckNumericDataTypeCompat modCreateBaseView modCreateDeleteTrigger modCreateHelperTable modCreateInsertTrigger modCreateWFBaseView modDropRowPermissions modEnumColumnPermissions modEnumRowPermissions modExecuteLocalTimeOutEvents modExecuteLocalTImeOutEventsforTable modGetBaseView modGetColListPermissions modGetDataForUpdate modGetDependantObjectList modGetExecutePermissions modGetParentforDetail modGetPermissionsJoinColumn modGetProc modGetUniqueObjectName modGrantColumnPermissions modInsertObjects modIsMember modProcessTable modRemoveAllModObjects modRemoveTable modRemoveWorkflow modRevokeColumnPermissions modVerifyIdentifierName |
Identifying Database Table Hierarchy
The Access Workflow Designer uses a database table hierarchy to manage the core features for a team solution. Tables can be identified as main, detail, or lookup tables.
When a table is added to the Access Workflow Designer as a main table, foreign-key constraints for the table are evaluated, and all child detail and lookup tables are added. For each table added to the table hierarchy in the team solution, a record is stored in the modObjects table in the database. The corresponding key-constraint column information is stored in the modColumns table. These tables are created during the registration of the database.
For each table identified as a main or detail table, the following items are created and added to the modObjects table:
| Table | modHelper<Table Name> |
|---|---|
| View | <Table Name>View |
No objects are created for lookup tables, but an entry is made in the modObjects table for the lookup table.
In addition, permissions changes occur on the main and detail tables at this time.
Defining a Workflow Process
When you add a workflow process to a main or detail table in your solution, the following objects are created:
| Tables | <Table Name>Workflow <Table Name>WorkflowActions |
|---|---|
| View | <Table Name>WorkflowView |
| Triggers | <Table Name>DeleteTRMod <Table Name>InsertTRMod <Table Name>UpdateTRMod |
If you choose to have the Access Workflow Designer create a new workflow lookup keyword column for you, the following changes are made:
- A column named modStateID is added to your main or detail table.
- A new table named <Table Name>StateLookup is created with the lookup values you specified in the wizard.
- A foreign-key constraint is created between your main or detail table and the new <Table Name>StateLookup table.
If you use an existing lookup table and add new lookup values, the only change to your solution is the addition of these values in the specified lookup table.
Making a Solution Available Offline
When offline replication is made possible for your solution, 6 tables and 24 stored procedures are added to your solution database. None of these modify any existing objects in your database.
When a publication is created, entries are made into the offline tables to support Access Workflow Designer in replicating offline data and merging it back correctly.
The following objects are added to a solution database during installation of replication components:
| Tables | modPublications modPubObjects modReplIncludeList modSubObjects modSubscriptions modWebFiles |
|---|---|
| Stored Procedures | modReplInsertPubObjects modReplIncludeObject sp_modReplAddPubObject sp_modReplCheckPublication sp_modReplCleanupPubObjects sp_modReplCreateIndex sp_modReplDeleteSub sp_modReplGetBaseView sp_modReplGetCachedVersions sp_modReplGetObjectChanges sp_modReplGetPublicationName sp_modReplInsertSub sp_modReplInsertSubObject sp_modReplInsertUserSubObject sp_modReplProcessTable sp_modReplSetNotForReplOnColumns sp_modReplSetSubState sp_modReplSubscribed sp_modReplTablePermissions sp_modReplUpdatePOV sp_modReplUpdateSubObjectVersions sp_modReplUpdateVersions sp_modReplUpdateVersions2 sp_modReplValidateObject |
Removing Access Workflow Designer Components
Although the uninstall portion of Microsoft Office Developer Setup successfully removes the modSystem database, as well as new objects created in the SQL Server Master database, a full uninstall may not be desirable.
For example, you may want to return only one database to its previous state, while leaving other team solutions on the server. In such cases, it is highly recommended you restore your database using a backup or DTS to transfer objects and data to a new, clean database.
That being said, however, you can remove the Access Workflow Designer extensions to the database manually that are described earlier.
To remove these database extensions manually (perform these steps in this order):
- Remove replication components.
- Remove workflow objects (triggers, workflow tables, and views).
- Remove main and detail table objects (helper tables and views).
- Remove registration objects (stored procedures, views, and tables).
- Delete the entry for the solution in the modApplications table in the modSystem database.
For specific instructions for removing these objects, see the following sections.
Removing Replication Components
It is recommended you use the Access Workflow Designer to remove replication components.
To remove replication components from a team solution
- Open the solution in the Access Workflow Designer, and select the database node in the Object List.
- On the Offline tab, choose the Uninstall Offline Replication Components option.
This option removes the objects identified in the “Making a Solution Available Offline” section earlier.
Removing Workflow Objects
Triggers on main and detail tables should be removed from those tables that are designated workflow tables to stop the enforcement of workflow actions and row permissions.
Use SQL Server Enterprise Manager to remove the triggers for all main and detail tables. Then, workflow tables and workflow views should be removed using SQL Server Enterprise Manager.
To remove workflow objects
- In SQL Server Enterprise Manager, open the node for the solution database.
- To remove triggers, select the Tables node, and right-click a main or detail table. From the shortcut menu, choose All Tasks, and then choose Manage Triggers. Select each workflow trigger from the Name list, and then click Delete. After deleting all of the triggers, click OK. Repeat for each table noted as a main or detail table.
- To remove workflow tables, right-click a workflow table. From the shortcut menu, choose Delete. Repeat for each workflow table.
- To remove workflow views, select the Views node, and right-click a workflow view. From the shortcut menu, choose Delete. Repeat for each workflow view.
For a list of specific workflow tables, views, and triggers, see the “Defining a Workflow Process” section earlier.
Removing Database Table Objects
Data helper tables and views used to enforce Access Workflow Designer features should be removed next. Use SQL Server Enterprise Manager to delete the tables and views listed in the “Identifying Database Table Hierarchy” section earlier.
To remove table hierarchy objects
- In SQL Server Enterprise Manager, open the node for the solution database.
- Select the Tables node, and right-click a helper table. From the shortcut menu, choose Delete. Repeat for each helper table.
- Select the Views node, and right-click a view. From the shortcut menu, choose Delete. Repeat for each view.
Removing Registration Objects
All the stored procedures, tables, and views created when the team solution is registered should be removed next. Use the SQL Server Enterprise Manager to remove these objects.
To remove registration objects
- In SQL Server Enterprise Manager, open the node for the solution database.
- Select the Stored Procedures node, and right-click a stored procedure. From the shortcut menu, choose Delete. Repeat for each stored procedure.
- Select the Tables node, and right-click a table. From the shortcut menu, choose Delete. Repeat for each table.
- Select the Views node, and right-click a view. From the shortcut menu, choose Delete. Repeat for each view.
For a list of specific objects, see the “Installing Access Workflow Designer” section earlier.
Updating the modSystem Database
The entry relating to the solution database you are cleaning must be removed from the modApplications table in the modSystem database. Use SQL Server Enterprise Manager, and open the modApplications table to view its contents. Delete the entry that relates to the database you have just cleaned.
To remove the solution entry in the modSystem database
- In SQL Server Enterprise Manager, open the modSystem database.
- Select the Tables node, and right-click the modApplications table. From the shortcut menu, choose Open Table, and then choose Return all rows.
- Delete the entry relating to the solution database you want to remove.
For More Information
For the latest information about Microsoft Access Workflow Designer for SQL Server, see the MSDN Office Developer Center at http://msdn.microsoft.com/office/.
To access Knowledge Base information, consult the Product Support section of the Microsoft Office Developer Web site.
For information about developing team solutions, consult the Access Workflow Designer Developer’s Guide online documentation.