Microsoft KB Archive/281126

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

Article ID: 281126

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q281126



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 &quot;SP_HELPTABLE tablename&quot;. 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 &quot;Generate SQL Script&quot; 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 &quot;commit tran&quot; 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-

</li> 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.</li></ul>

Additional query words: system tables sysobjects sysconstraints 217700 error

Keywords: kbsqlprog kbprb KB281126

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.