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)
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 |
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.
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.
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 |
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.
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:
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.
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 |
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):
For specific instructions for removing these objects, see the following sections.
It is recommended you use the Access Workflow Designer to remove replication components.
To remove replication components from a team solution
This option removes the objects identified in the “Making a Solution Available Offline” section earlier.
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
For a list of specific workflow tables, views, and triggers, see the “Defining a Workflow Process” section earlier.
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
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
For a list of specific objects, see the “Installing Access Workflow Designer” section earlier.
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
For the latest information about Microsoft Access Workflow Designer for SQL Server, see the Microsoft Office Developer Web site at http://msdn.microsoft.com/OfficeDev/.
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.