14
Attempt to insert duplicate row in table '%.*s' with index '%.*s' in database '%.*s'. Could drop and recreate index with ignore duprow or allow duprow.
Tables with clustered indexes do not allow duplicate rows. Error 2615 occurs:
When you try to insert a duplicate row in a table that already has a clustered index.
When you try to upgrade to Adaptive Server Enterprise
version 12.0.x or version 12.5.x, and encounter duplicate rows in
the syskeys system table. This occurs because
the upgrade exposes a previously hidden column, “spare1”,
setting its contents to 0 (zero). If the database previously contained
two or more syskeys rows that were identical
except for this column, attempting to set syskeys.spare1 to
zero will produce 2615 errors, accompanied by 3462 errors ("Database
'%.*s': upgrade could not install optional upgrade
item '%d'. Please refer to previous error messages to determine
the problem. Upgrade will continue."
).
The duplicate syskeys entries are not caused by the upgrade process; rather, the upgrade process reveals such entries if they exist.
If the duplicate values are a result of incorrect data, change the data and rerun the transaction. Otherwise, decide whether to allow or prevent duplicate rows in the table where the clustered index will be created. Use one of the following procedures.
ignore_dup_row and allow_dup_row are mutually exclusive options of the create index command. Do not use them together in the same statement, which will cause error 1916.
Create the index with the allow_dup_row option:
1> create clustered index <index_name> on <table_name> 2> with allow_dup_row 3> go
Subsequent update and insert commands can create duplicate rows after the allow_dup_row option is set.
WARNING! Do not use this procedure if you have a unique index on any column or columns in the table. Unique indexes are only used on tables where no duplicate rows exist.
Create the index with the ignore_dup_row option:
1> create clustered index <index_name> on <table_name> 2> with ignore_dup_row 3> go
Existing duplicate rows will be deleted from the table as the index is created. Subsequent attempts to enter a duplicate row with insert or update are ignored and the insert or update is cancelled with an informational message. If the insert or update is part of a larger transaction, the rest of the transaction will continue as usual.
If the error occurs during upgrade, begin by making sure that you are upgrading using one of the recommended migration paths:
Pre-12.0 to 12.0.x
Pre-12.0 or 12.0.x to 12.5.0.1 (interim release) or later
If you do not upgrade using one of these paths, error 2615 may be raised during a required upgrade step and that the error will abort the upgrade. By following the recommended path, you may encounter the error, but it is benign and will not cause the upgrade to abort.
After the upgrade completes, use the following steps after the upgrade to correct the problem by removing duplicate syskeys rows:
Identify duplicate rows.
Execute the following query in the database named in the 2615 error to identify the duplicate syskeys entries:
1> select id,
2>'Primary object' = object_name(id), 3> type, 4> depid, 5> 'Dependent object' = object_name(isnull(depid,0)), 6> spare1 7> from syskeys k1 8> where exists (select spare1 9> from syskeys k2 10> where k1.id = k2.id 11> and k1.type = k2.type 12> and k1.spare1 != k2.spare1) 13> go
Identify the duplicate syskeys entries that have two identical (id, type) pairs.
To see if there are more than two entries with identical (id, type) pairs, execute this query:
1> select * from syskeys 2> where id = <test_id> and type = <test_type> 3> go
The duplicates are the rows where ALL columns except spare1 match.
Eliminate duplicate syskeys entries.
Allow updates to system tables:
1> sp_configure "allow updates", 1 2> go
Delete all but one of each duplicate set. Repeat the following command once for each duplicate:
1> delete syskeys 2> where id = <duplicate_id> 3> and type = <duplicate_type> 4> and spare1 = <one_of_the_duplicates> 5> go
(Optional) Before disallowing updates to system tables (step d), you can execute the following commands to prevent other duplicates:
1> update syskeys 2> set spare1 = 0 3> where spare1 != 0 4> go
After removing all duplicates, disallow updates to system tables:
1> sp_configure "allow updates", 0 2> go
Refer to “create index” in the Reference Manual: Commands for information about ignore_dup_row and allow_dup_row.
All versions