Article ID: 937757 - View products that this article applies to.
In Microsoft SQL Server 2005, you can run the ALTER SCHEMA statement to move an object from one schema to another schema. However, when you run the ALTER SCHEMA statement, you may receive the following "Msg 15530" error message:
This error message may be confusing if the destination schema does not appear to contain an object that has the same name as the object that you are trying to move.
Msg 15530, Level 16, State 1, Line 1
The object with name "ObjectName" already exists.
This issue occurs if the following conditions are true:
Internally, SQL Server stores information about the primary key for each table as an object in each schema. When you move a table to another schema, the primary key object for the table is also moved to the other schema. In the scenario that is mentioned earlier, the ALTER SCHEMA statement fails because the destination schema already contains a primary key ABC object.
This error message is not clear because the error message states that the table is the duplicate object. However, the primary key object that it associated with the table is actually the duplicate object.
To resolve this issue, rename the primary key for the table that you want to move. Use a name that does not appear as a primary key in the destination schema.
When you are experiencing this issue, you receive an "Error 2601" error message if you use SQL Profiler to trace the operation. Error 2601 is a duplicate key error. The error message resembles the following:
This error occurs on object 34, index id 2. The object is the sys.sysschobjs system table. The index is nc1. Index nc1 is defined to be unique on the following columns:
Error: 2601, Severity: 14, State: 1
For example, in the scenario that is mentioned earlier, assume that the value in the nsid column for Schema A is 5. Additionally, assume that the value in the nsid column for Schema B is 1. You run the following statement to query the sys.sysschobjs system table:
The query returns the following information:
This information shows that, when you run the ALTER SCHEMA statement to try to change the nsid from 5 (Schema A) to 1 (Schema B), you are trying to add a duplicate row in the sys.sysschobjs system table for index nc1. Therefore, error 15530 occurs, and you receive the error message that is mentioned in the “Symptoms” section. The underlying behavior of error 15530 is that error 2601 occurs on the sys.sysschobjs system table when you run the ALTER SCHEMA statement.
nsclass nsid name ---------------------------------------- 0 1 ABC 0 5 ABC
Steps to reproduce the behavior
Article ID: 937757 - Last Review: November 20, 2007 - Revision: 1.2