PRB: Updating a Database Sysindexes Table

ID: 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 "my_table", 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 : kbprg SSrvServer
Version : 4.2
Platform : OS/2
Issue type :