ID Number: Q65057
1.00 1.10 4.20
OS/2
Summary:
SYMPTOMS
The DBCC checktable routine asks you to update the dpages column in
the SYSINDEXES table. However, attempt to update sysindexes using a
query such as the following
update sysindexes
set dpages = xxx
where name = 'my_table'
results in the 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.
RESOLUTION
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 above query, 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.