Next Object id already assigned

I originally posted this problem under "stored proc won't compile" in
the programming group, but now that I've found more detail, I think it
is better suited to this group with a different title.
I can't create any objects in master. What I've found is that the
dbi_nextid is already assigned to another object. Can anyone offer
any suggestions? DBCC checkcatalog and dbcc checkdb return no errors.
create proc simpleproc as select * from sysobjects
returns
Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
There is already an object named 'simpleproc' in the database.
Further research has found the the dbi_nextid is already in use.
DBCC TRACEON(3604)
DBCC DBINFO('master')
DBCC TRACEOFF(3604)
returns
dbi_nextid = 1537440551
select name, type, id, crdate from sysobjects where id = 1537440551
returns
name type id crdate
sp_dropmergearticleP 15374405512003-03-09 18:00:50.177
select max(id) from master.dbo.sysobjects
returns
2145442717
@.@.version =
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
> There is already an object named 'simpleproc' in the database.
>
> Further research has found the the dbi_nextid is already in use.
Why did you go further? It appears you already have a procedure named
simpleproc, and this is why you can't create another one. How about change
the name to splungeproc and see if that works. Why are you creating these
procs in master anyway? How many objects are in master now?
http://www.aspfaq.com/
(Reverse address to reply.)
|||I went further because I need to get the proc created. It is an SMTP
mail proc that I ultimately need to create. I used the simpleproc
example so people would not have to wade through lines of code. I am
sure simpleproc does not exist
My problem is I can't create any object in master - regardless of the
name or type. Tables, views, stored procs all receive the same error -
object already exists. If I try and drop the object first, I get that
it does not exist.
select * from sysobjects where name = 'simpleproc'
go
drop proc dbo.simpleproc
go
create proc dbo.simpleproc as select * from sysobjects
returns
(0 row(s) affected)
Server: Msg 3701, Level 11, State 5, Line 3
Cannot drop the procedure 'dbo.simpleproc', because it does not exist in
the system catalog.
Server: Msg 2714, Level 16, State 5, Procedure simpleproc, Line 1
There is already an object named 'simpleproc' in the database.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||What about my other questions? Why does this have to be in master? How
many objects are in master now?
http://www.aspfaq.com/
(Reverse address to reply.)
"Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
news:uA3PgWCaEHA.2840@.TK2MSFTNGP11.phx.gbl...
> I went further because I need to get the proc created. It is an SMTP
> mail proc that I ultimately need to create. I used the simpleproc
> example so people would not have to wade through lines of code. I am
> sure simpleproc does not exist
|||A count of sysobjects returns 1305.
I am putting the proc in master because on all my other servers it is in
master. This is an enterprise wide proc developed for standard use in
sending emails from SQL. I don't want it to be in master on all the
other servers and a different database on this one. In hindsight, we
might have made this an extended stored proc, but there is too much code
to go back and change it now.
But all that is really beside the point. I should be able to create
objects in master - whether it is advisable or not - and I can't because
the "next object id" is already being used.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||This sounds like a known issue - please call Product Support who will assist
you.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
news:u2A3XnCaEHA.3996@.TK2MSFTNGP12.phx.gbl...
> A count of sysobjects returns 1305.
> I am putting the proc in master because on all my other servers it is in
> master. This is an enterprise wide proc developed for standard use in
> sending emails from SQL. I don't want it to be in master on all the
> other servers and a different database on this one. In hindsight, we
> might have made this an extended stored proc, but there is too much code
> to go back and change it now.
> But all that is really beside the point. I should be able to create
> objects in master - whether it is advisable or not - and I can't because
> the "next object id" is already being used.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thank you Paul!!
Since you mentioned a known problem, I found KB Article
http://support.microsoft.com/default...48&Product=sql
And it did the trick. I could not get the supplied script to work, so
I simple coded a drop and create and duplicated the lines 50 or so
times. Then ran that until the object created successfully.
Thanks again
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message news:<e0onLTHaEHA.3888@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> This sounds like a known issue - please call Product Support who will assist
> you.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Stephanie Harrell" <stephanie.harrell@.stateauto.com> wrote in message
> news:u2A3XnCaEHA.3996@.TK2MSFTNGP12.phx.gbl...