Upgrading Workbench database to Wise Package Studio 7 SP2 fails

Products

• Symantec Products

Issue/Introduction

When upgraded from Wise Package Studio 7 SP1 to Wise Package Studio 7 SP2, everything updates properly with the exception of the Workbench database. The Workbench database in Wise Repository Manager shows that it failed to update.

Below is the error output from SQL Analyzer:

Server: Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'ProjectDirEx' in
table 'Projects' is specified more than once.
Server: Msg 1779, Level 16, State 1, Line 2
Table 'Tasks' already has a primary key defined on it.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.

Cause

The SQL script is trying to specifically delete PK_TASKS_0AD2A005 and UQ__Tasks__0BC6C43E from the Tasks table. The user's indexes have different names, PK_TASKS_2A4B4B5E, UQ_TASKS_0BC6C43E. Since the numbers in the customer's indexes do not match, they are not being dropped and the proper key cannot be added, which results in the errors and failure.   

Resolution

There are two ways to correct the problem:

1. Open up the Workbench database in Enterprise Manager for SQL 2000 or SQL Server Management Studio for SQL 2005. 
   a. Open up the Workbench Database in the SQL Enterprise Manager or SQL Server Management Studio for SQL 2005
   b. Go to the Tasks Table.  Right click and select the design table
   c. From the toolbar select the Manage Relationships button (3rd button from the right)
   d. The Properties dialog opens. Select the Index/Keys tab
   e. In the Selected Index name make sure the PK_Tasks is selected
   f. For the Type field, press the Delete button.
   g. Re-run the script in the SQL Query Analyzer.
   h. From the Wise Repository Manager press the Upgrade button. It should successfully complete.
   
2. Edit the SQL query to match the numbers shown for PK_TASKS_XXXXXXXX and UQ_TASKS_XXXXXXXX in the Indexes/Keys tab and then rerun the script in the SQL Query Analyzer. 

Applies To

Windows 2003 Server*