Microsoft KB Archive/65057

{|
 * width="100%"|

PRB: Updating a Database Sysindexes Table

 * }

Q65057

-

The information in this article applies to:


 * Microsoft SQL Server for OS/2, version 4.2

-

SYMPTOMS
An attempt to update sysindexes using a query such as the following,

  update sysindexes set dpages = xxx where name = 'my_table'

results in the following message from SQL Server:

Msg 412, Level 16, State 1:

Cannot update more than 1 Sysindexes row at a time

However, the following query only returns one row:

  select * from sysindexes where name = 'my_table'

CAUSE
Because the sysindexes table contains one row for each clustered index, each nonclustered index, and each table without a clustered index, it is possible for the table to have more than one entry that corresponds to one data object. To protect against inadvertently modifying duplicate values in sysindexes, SQL Server requires that your UPDATE statement include the columns that compose the primary key for the sysindexes table. These columns are ID and INDID.

WORKAROUND
To perform an update of the dpages value of a table named &quot;my_table&quot;, you must first find the values of ID and INDID for your table by using the following query:

  select ID, INDID from sysindexes where name = 'my_table'

INDID will contain the following:

   0  for a table with no clustered index 1 for a clustered index >1 for a nonclustered index

Using the values returned from the query above, you can now use a query of the following form to update the sysindexes table:

  update sysindexes set dpages = xxx where   ID = xxx and  INDID = x    <--- (either 0 or 1)

There are several more things to note concerning the updating of the sysindexes table. Before you can update any system table, the system administrator (SA) must enable updates to system catalogs. This can be done by using the sp_configure stored procedure with the RECONFIGURE WITH OVERRIDE option, or by using the dynamic options under the Configuration menu of SAF.

After sysindexes has been modified, be sure to execute the CHECKPOINT statement in your database.

Additional query words: prodsql

Keywords : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbZNotKeyword3 kbSQLServ420OS2