Troubleshooting Database Issues

Article
09/14/2006

This section addresses issues relating to the Microsoft® SQL Server™ databases used in a workflow application, including issues concerning tables, views, and other database objects. If you are creating workflow applications on existing databases or creating new databases, then it is recommended you review the information included here.

Schema Requirements and Limitations

When designing the schema for your project database, it is important to consider the following issues.

To enable row-level permissions on a user table, the table must have an integer primary key column or an identity column. Use integer identity columns as primary keys. This is a requirement for main and detail tables and is a general recommendation for all tables. If your table does not have such a column, you should add an identity column before enabling row-level permissions.
Tables with timestamp columns cannot be replicated if you are using a version of SQL Server earlier than SQL Server 2000.
Tables with columns larger than 6,000 bytes cannot be replicated.
Nvarchar columns in a workflow-enabled database must not exceed 2,000 bytes.
The modSystem database has a reserved name. The tables in the modSystem database and the Workflow Designer tables in a workflow-enabled database have reserved names — for example, "modObjects" and "modColumns." Do not use these names anywhere else in your project.
By default, destination databases replicated on a client computer have the same name as the source databases. If an existing database on the client already uses the default name, an auto-generated name is given to the destination database. The new name is stored in the modApplications table in the modSystem database. You cannot rename your database during replication.

Views and Stored Procedures

When creating a data access page for a main or detail table, always bind your controls to the base view (or a view built on top of the base view) — not to the table itself. You must specify a unique table for a data access page based on a view so that the table can be updated, and for the Office Developer Workflow Toolbar control to function.

When a table is added to the table hierarchy, views are created for each user table. For example, the Issues table in the Issue Tracking sample has the following associated view and stored procedure:

IssuesView A view that implements row-level permissions and column permissions for the Issues table.
IssuesUpdate A stored procedure that returns an updateable result set with all the columns for which the current user has select permissions.

Table Schema Changes to Avoid

Do not rename tables after you have added them to the table hierarchy.
Do not change the column name or data type of the modStateID column.
Do not change the name of an identity or primary key column for tables that have row-level permissions enabled. Triggers and views based on such tables refer to the columns by name.

Note You can change the names of other columns, but you must make sure there are no additional dependencies on those column names in your application.

Invalid-Database Repair

Manually removing from the database or modifying any of the tables used internally by the Workflow Designer might make the application invalid and disable the functionality of the tools. If this happens, the database must be reregistered as a workflow application and the workflow process re-created.

You can repair your invalid database by calling the modDropSchema stored procedure from the modSystem database and then opening your database in the Workflow Designer. When the database is registered as a workflow-enabled database, you can re-create your workflow.

Note Before running the modDropSchema stored procedure, you might want to save the workflow script and the workflow tables (modWorkflow and modWorkflowActions) and use them to re-create the workflow process after the workflow application has been repaired.

Note To make a backup of the script, copy the entire contents of the Code Editor, and paste it into a text editor, such as Microsoft® Notepad.

Note To make a backup of the tables, export them to another SQL database. Although you cannot directly import these tables to re-create your workflow, you can use them as a guide to help you remember your states and events.

Synchronization of the Workflow Application User Directory

The computer you are using to synchronize the workflow application User Directory must have the file SQLNS.dll, which is installed with SQL Server, registered on it.

To resolve this issue, perform all synchronization scheduling on a computer that has SQL Server installed.

.mdf Files and Detaching Databases

Every SQL Server database is associated with an .mdf file. For example, the Issue Tracking sample database has the file IssueTracking_Data.mdf. When you detach the database, SQL Server deletes the corresponding .mdf files.

Problems might occur if you reinstall SQL Server without detaching your databases first. When you reinstall, SQL Server removes the databases but does not remove the .mdf files. When the Workflow Designer encounters .mdf files that are not attached to SQL Server, many functions can fail. If you have problems installing Workflow Designer components, opening databases in the designer, or doing other database-related activities, verify that the .mdf file exists and is attached to SQL Server.

To resolve these problems, either delete the .mdf files or attach the files to SQL Server. For details, see "Attaching and Detaching Databases" in the SQL Server Books Online.

Restoration of a Database with a New Name

If you delete a workflow-enabled database and later restore that database from a backup but give the database a different name, the workflow application based on that database will no longer function. For example, ProjectA is a registered workflow application on database "ProjectA." The user makes a backup of this database and then deletes the original "ProjectA" database. Later, the user restores the database but renames it "SolutionA."

There are several elements that must be updated with the new database name to make the application function again. Most of these should be familiar to database developers. First, any user interface elements, such as forms or data pages containing connection information, must be updated with the new name of the database. In addition, some workflow specific infrastructure must be updated, specifically the modApplication table.

The Workflow Manager for SQL Server tracks all workflow applications on a server using entries in the modApplications table in the modSystem database. Each application stores its name and the name of the associated database in a local modApplication table in the workflow-enabled database. The Workflow Manager programmatically keeps the local modApplication table and the modSystem modApplications table synchronized. When a user restores a database and gives it a new name, the database name entry in the local modApplication table is not updated automatically, and these entries become unsynchronized. Therefore, when the Workflow Manager connects to the server and displays the names of the applications, it still shows "ProjectA," even though there is no database with that name. Because the name in the modApplication table does not match the name of the actual database, the workflow application does not function.

To correct the database name entry, you must update the local modApplication table in the "SolutionA" database. Although manually changing the workflow system tables is not recommended, in this case, it is necessary to make the application function as expected.

To update the local modApplication database name column

Start SQL Server Enterprise Manager.
Expand the nodes in Enterprise Manager until you can see your databases.
Select the restored database "SolutionA."
Expand the Tables node to display all the database tables.
Right-click the modApplication table, point to Open Table, and click Return All Rows.
Update the Database column with the name of your restored database, "SolutionA."

The next time you open the Workflow Manager and click Refresh, you will see the name that matches the database.

No Refresh of Solution Explorer

In the Workflow Designer, the Solution Explorer might not refresh automatically when you add new main tables to your application. Even pressing F5 for manual refresh might not refresh the window.

To work around this problem, close and reopen the Solution Explorer to update the window.

Share via