PRB: System Tables Missing Primary Key Information After Upgrade from SQL Server 6.x to SQL Server 7.0 or SQL Server 2000 (281126)

SYMPTOMS

The primary key (PK) constraint for some user tables may not upgrade correctly from Microsoft SQL Server 6.5 to Microsoft SQL Server 7.0 or to Microsoft SQL Server 2000.

After an upgrade from Microsoft SQL Server 6.5 to Microsoft SQL Server 7.0 or to Microsoft SQL Server 2000, the new Microsoft SQL Server upgraded user database may not have the primary key constraint on some user tables as a consequence of the following Microsoft SQL Server 6.5 conditions:

The primary key shows up when you run "SP_HELPTABLE tablename".
The affected key does not show up in the SQL Server 6.5 Enterprise Manager (SEM).
The primary key does not show up when you run the "Generate SQL Script" from SQL Server 6.5 Enterprise Manager.
When you try to edit the table in the SQL Server 6.5 Enterprise Manager, this error message occurs:
Microsoft SQL-DMO
Error 21770: The name 'PK_str_nshr_comment' was not found in the Keys collection.

CAUSE

The primary key constraint does not show up in the upgraded SQL Server user database user tables, because there is missing data in the sysobjects system table for the user database on the 6.5 SQL Server server.

The Category column in the sysobjects system table is missing the bit 512 (0x200), which means that the table has a primary key constraint. Otherwise, Generate SQL Script generates a script for the creation of the primary key constraint.

A primary key constraint needs to have:

The Status column in the sysconstraints system table must be set to 1.
The Category column in the sysobjects system table must have the bit 512 (0x200) set.
The Colid column in the sysobjects system table must be set to 0.

WORKAROUND

Here are two methods you can use to work around this behavior:

Run the script that follows to correct the missing bit.

However, before you proceed with the script, run sp_helptable on the user table to check how many rows expect modification and then compare which primary key constraints are not correctly set. Next, run this command from ISQL_W:
```
begin tran 
update sysobjects set category = category + 512
from sysobjects o, sysconstraints c
where o.type = 'U' and
o.id = c.id and
(c.status & 1) = 1 and
(o.category & 512) = 0 and
c.colid = 0
						
```
If the number of rows modified are what you expect, then run "commit tran" to complete the corrective process. After you perform this step, the SQL Server 6.5 user database is ready for the user database to be upgraded again. -or-
An alternative solution is to drop the primary key constraint, and then re-create it. This can be done on the affected SQL Server 6.5 user database table before you perform the upgrade again, or performed on the upgraded SQL Server user database table.