Share via

Troubleshooting: Database Issues

  • Article

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.

This section addresses issues relating to the SQL Server databases used in a team solution, including issues concerning tables, views, and other database objects. If you are creating team solutions from existing databases or creating new databases, it is recommended you review the information included here.

For information about specific error messages, see Error Messages.

Schema requirements and limitations

When designing the schema for your solution 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.

  • The status column in a workflow-enabled table must be of type integer and should have an associated lookup table that holds the name of the workflow states. The lookup and user tables should be linked by a primary key/foreign key constraint.

  • Tables with timestamp columns cannot be replicated.

  • Tables with columns larger than 6,000 bytes cannot be replicated.

  • The nvarchar column in a solution database must not exceed 2,000 bytes.

  • The modSystem database has a reserved name. The tables in the modSystem database and the Access Workflow Designer tables in a solution database have reserved names—for example, “modObjects” and “modColumns.” Do not use these names anywhere else in your solution.

  • 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 solution database during replication.

For additional information about schema requirements, see Team Solution Development Guidelines.

Views and stored procedures must be created for each user table

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 to be updateable and for the workflow toolbar to function. For more information, see Creating Data Access Pages.

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 solution 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 once you have added them to the table hierarchy.

  • Do not change the column name or data type of the workflow state 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 solution. For details about identifying foreign key constraints and selecting different keyword columns, see Detail Table General Tab (Tables Pane) and Lookup Table General Tab (Tables Pane).

Repairing an invalid solution database

Manually removing from the database or modifying any of the tables used internally by the Access Workflow Designer tools may make the solution invalid, which may seriously disable the functionality of the tools. If this happens, the solution database must be reregistered as a team solution.

You can repair your invalid database by calling the modDropSchema stored procedure from the modSystem database and then opening your database in the Access Workflow Designer. This opens the Database Registration wizard. Once the database is registered as a solution database, you can add the Access Workflow Designer services to the database. For details, see Registering a Solution Database.

****Note   ****Before running the modDropSchema stored procedure, you may want to save the workflow script and the workflow tables (modWorkflow and modWorkflowActions) and use them to recreate the workflow definition after the team solution has been repaired.

Note   To make a backup of the script, copy the entire contents of the SharedScripttab of each workflow process, and paste it into a text editor, such as Microsoft Notepad.

****Note   ****To make a backup of the tables, export them to another Access project (*.adp). Although you cannot directly import these tables to recreate your workflow, you can use them as a guide to help you remember your states and actions.

Problems synchronizing the user directory (modUserList table)

If you have trouble synchronizing the information in the user directory, it is possible you have an incompatible version of Microsoft Active Directory Service Interface (ADSI) installed.

If you have installed Microsoft Exchange 5.5 or later after installing the Access Workflow Designer server components, the version of ADSI on your computer may cause synchronization problems.

To resolve this issue, run ADS.exe from the Microsoft Office 2000 Developer CD-ROM.

Problems with .mdf files and dropping databases

Every SQL Server database is associated with an .mdf file located in the c:\mssql7\data directory. For example, the Issue Tracking database has the files c:\mssql7\data\IssueTracking.mdf. When you drop the database, SQL Server deletes the corresponding .mdf files.

Problems may occur if you reinstall SQL Server without dropping your databases first. When you reinstall, SQL Server removes the databases but does not remove the .mdf files. When Access Workflow Designer encounters .mdf files that are not attached to SQL Server, many functions may fail. If you have problems installing Access Workflow Designer components, opening databases in the designer, creating offline subscriptions, and so on, 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.

Tables and Solution pane may not refresh automatically

In the Access Workflow Designer, the Tables or Solution pane may not refresh automatically when you add new main tables using the Main Table Selection wizard. Even pressing F5 for manual refresh may not refresh the pane.

To work around this problem, close and reopen the Tables or Solution pane to update the pane.