Microsoft KB Archive/827448

= PRB: Creating New Database Objects Fails in a SQL Server Database =

Article ID: 827448

Article Last Modified on 12/2/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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 ' ' 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 ' ', 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  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.

Keywords: kberrmsg kbview kbtrigger kbstoredproc kbscript kbtsql kbserver kbdatabase kbtable kbsetup kbmessagebox kbprb KB827448

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.