When you try to create an object such as a table, a view, a DEFAULT constraint, a rule, a stored procedure, or a trigger in a Microsoft SQL Server database, you may receive an error message that states that the object already exists in the database. For example, if you try to create a new table, you may receive an error message that is similar to the following:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'TableName' in the database.
And if you try to drop the table that you tried to create earlier, you may receive an error message that is similar to the following:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'TableName', because it does not exist in the system catalog.
However, you may notice that the object that you tried to create does not exist in the database. If you query the sysobjects
system table, you may notice that the table that you tried to create earlier does not exist.
When you try to create a stored procedure, you may receive an error message that is similar to the following:
Msg 2601, Level 14, State 3
Attempt to insert duplicate key row in object 'sysprocedures' with unique index 'sysprocedures'
Msg 2811, Level 16, State 2
Cannot create procedure dbid 1, objid 498100815, with a group number of 1.
You may also notice a similar behavior when you try to upgrade an instance of SQL Server 2000 or SQL Server 2000 Service Pack 1 (SP1) to SQL Server 2000 Service Pack 2 (SP2) or Service Pack 3 (SP3). The SQL Server setup may display error messages that are similar to the following when you upgrade to SQL Server 2000 SP2 or SP3:
Server: Msg 134, Level 16, State 2, Procedure sp_droplogin, Line 4
The variable name '@loginame' has already been declared. Variable names must be unique within a query batch or stored procedure.
Server: Msg 2714, Level 16, State 5, Procedure sp_droplogin, Line 4
There is already an object named 'sp_droplogin' in the database.
To work around the problem, you must create many database objects so the object ID is incremented for each new database object created. You must continue to create database objects even if the CREATE statement that you run does not succeed. The new database objects will be created when the generated object ID value passes the range of object IDs that are already in use.
The following is a sample script that can be run on the affected database to generate object IDs until an unused object ID value is generated and the object is created successfully.Note
with your database name.
USE DatabaseNameGODECLARE @i int, @str1 varchar(255), @str2 varchar(10), @str3 varchar(255), @str4 varchar(255), @str5 varchar(255)SELECT @i = 0 WHILE (@i < 1000) BEGIN SELECT @str1 = 'CREATE TABLE test_table' SELECT @str2 = '(col1 int)' SELECT @str3 = @str1 + CONVERT(varchar(6), @i) + @str2 SELECT @str4 = 'DROP TABLE test_table' SELECT @str5 = @str4 + CONVERT(varchar(6), @i) EXEC (@str3) EXEC (@str5) SELECT @i = @i + 1 END