PRB: Creating New Database Objects Fails in a SQL Server Database (827448)

SYMPTOMS

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.

WORKAROUND

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 Replace DatabaseName with your database name.

USE DatabaseName
GO

DECLARE	@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

MORE INFORMATION

The following are the different variations of the problem:

The object ID is already in use and the existing object has rows in the syscolumns system table. When you try to create a stored procedure, a failure may occur with error number 134. This problem occurs because the syscolumns table already contains a row with the same values for the objid, the colid, and the paramid columns.
The object ID is already in use and the existing object has no rows in syscolumns. An attempt to insert a row into the sysobjects system table finds that the new objid already exists in the table. The attempt to insert the row is aborted and you may receive an error message with error number 2714 .
The object ID is already in use but the object that is associated with the object ID has been dropped.
An attempt to create an object succeeds, but subsequent attempts may still fail because of the other variations of the problem that is mentioned in this section.