Invalid Object Name - Weird Error - Help!

I have a really strange problem. It seems a table has gone invisible. It's
listed in SysObjects, but does not show up in the tables list.
When I execte "Select * From Object_Access_Levels", it raises the error "Invalid
Object Name".
If I try to [Drop Table Object_Access_Levels], it returns the error:
"Cannot drop the table 'Object_Access_Levels' because it does not exist in the
system catalog."
If I follow the ID for the table to Syscolumns and SysIndexes, all the records
are there.
This is a complete show-stopper. I cannot continue development until this table
is restored.
Here's a copy of the record from sysobjects:
name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,
schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltri
g,instrig,updtrig,seltrig,category,cache
"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
Does anyone have a clue how I can fix this problem?
I am using SQL2K with SP3a.
TIA,
-Steve-Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:e50xOxeYEHA.808@.tk2msftngp13.phx.gbl...
>I have a really strange problem. It seems a table has gone invisible.
>It's
> listed in SysObjects, but does not show up in the tables list.
> When I execte "Select * From Object_Access_Levels", it raises the error
> "Invalid
> Object Name".
> If I try to [Drop Table Object_Access_Levels], it returns the error:
> "Cannot drop the table 'Object_Access_Levels' because it does not exist in
> the
> system catalog."
> If I follow the ID for the table to Syscolumns and SysIndexes, all the
> records
> are there.
> This is a complete show-stopper. I cannot continue development until this
> table
> is restored.
> Here's a copy of the record from sysobjects:
> name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,
> schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltri
> g,instrig,updtrig,seltrig,category,cache
> "Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
> 02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
> Does anyone have a clue how I can fix this problem?
> I am using SQL2K with SP3a.
> TIA,
> -Steve-
>|||You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.
>--Original Message--
>I have a really strange problem. It seems a table has
gone invisible. It's
>listed in SysObjects, but does not show up in the tables
list.
>When I execte "Select * From Object_Access_Levels", it
raises the error "Invalid
>Object Name".
>If I try to [Drop Table Object_Access_Levels], it
returns the error:
> "Cannot drop the table 'Object_Access_Levels' because
it does not exist in the
>system catalog."
>If I follow the ID for the table to Syscolumns and
SysIndexes, all the records
>are there.
>This is a complete show-stopper. I cannot continue
development until this table
>is restored.
>Here's a copy of the record from sysobjects:
>name,id,xtype,uid,info,status,base_schema_ver,replinfo,pa
rent_obj,crdate,ftcatid,
>schema_ver,stats_schema_ver,type,userstat,sysstat,indexde
l,refdate,version,deltri
>g,instrig,updtrig,seltrig,category,cache
>"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,
"07/12/2002
>02:09pm",0,272,0,"U ",1,115,0,"07/12/2002
02:09pm",0,,,,0,2560,0
>Does anyone have a clue how I can fix this problem?
>I am using SQL2K with SP3a.
>TIA,
>-Steve-
>
>.
>|||Thanks Aaron,
The table has been in use for over a year. Everything was fine last week during
development. Then yesterday, I tried to run the app and got the error during the
load.
This SQL command returns an empty result.
[SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels']
But as I stated, the table is listed in the SyObjects, SysIndexes, and
SysColumns.
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
It looks like SQL's internal table got hosed. Is there any way to fix this
without completely rebuilding the database?
-Steve-
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239t8NTfYEHA.4004@.TK2MSFTNGP10.phx.gbl...
Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||BTW, I just checked the ID 1840725610, it doesn't belong the Object_Access_Levels
table. It belongs to a couple of params. I think this DB is really hosed!
-Steve-
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:%23JR2N$oYEHA.1764@.TK2MSFTNGP10.phx.gbl...
Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||Steve,
Any idea what error message(s) you were getting? If
error 2513, check SQL Books Online for steps to possibly
resolve the inconsistency. Please ensure you have a
backup or copy of the .mdf & .ldf before you do anything.
HTH
Darren Fuller
>--Original Message--
>Hi Mark,
>Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
>Returns this error:
>[Table Corrupt: Object ID 1840725610
(object '1840725610') does not match between
>'SYSCOLUMNS' and 'SYSOBJECTS']
>Is this fixable without rebuilding the database?
>Thanks,
>-Steve-
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
>You may want to run DBCC CHECKCATALOG to check the system
>tables.
>Mark Baekdal
>www.dbghost.com - the only true Database Change Manager
>for SQL Server.
>
>.
>|||After much wasted time, I restored an old copy of the db. I was under the
impression (apparently a false one) that SQL server didn't have these types
corruption problems.
Thanks everyone for your suggestions.
-Steve-|||Your impression is not false, we don't have these types of corruption
problems.
However, hardware can and does introduce all manner of corruptions that
manifest themselves in various ways. I would check your NT event logs and
SQL errorlog for IO susbsystem errors. Before restoring from your backup, I
would have recommended running DBCC CHECKDB to check for other corruptions.
In future, to avoid wasting time, you should call Product Support who will
be able to help you pinpoint the problem very quickly.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:ermVPB6YEHA.1048@.tk2msftngp13.phx.gbl...
> After much wasted time, I restored an old copy of the db. I was under the
> impression (apparently a false one) that SQL server didn't have these
types
> corruption problems.
> Thanks everyone for your suggestions.
> -Steve-
>