SQL Server Performance Forum – Threads Archive

Database – System views

Hi,
I have restored a backup obtained from SQL 2000 to SQL 2005. When I run DBCC checkcatalog on a database, the following error comes up Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=1) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=2) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=1640392913) of row (object_id=1640392913,column_id=3) in sys.columns does not have a matching row (object_id=1640392913) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=1640392913) of row (object_id=1297439696) in sys.objects does not have a matching row (object_id=1640392913) in sys.objects —> Sys.columns and sys.objects are the system views of a database. —> When I do select * from sys.coulmns where object_id=1640392913, the result returns three rows.And these three rows of sys.columns do not have macthing rows in sys.objects. We cannot alter any of the system objects in SQL 2005. How can we fix this? Thanks Roopa

If I understand correctly, you have taken a backup from 2000 and restored it to 2005? Assuming this is correct, do you know if the original backup is a good backup? Have you run the upgrade wizard on the 2000 database to be sure there are no obvious compatability issues? Trying to "fix" this problem is problematic. Ideally, you want to prevent this problem, not fix it. ——————————–
Brad M. McGehee, SQL Server MVP
http://www.sqlbrad.com

I don’t think it is compatability issue and upgrade advisor may not show these things… Restore the sql 2000 backup on to SQL server 2000 server with same build and run the dbcc checkdb and see if you get any errors… On existing sql server 2005 you can alter the system objects if you want to fix the problem by using dedicated Admin Connection (DAC)… First figure check it out what are the object it is referring to…
Run the DBCC DBREINDEX on sql server 2005 db and run the dbcc checkdb again and see what you get…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

You cannot modify the system tables or values in SQL 2005, not like SQL 2k olden days.
To see what are they run select object_id, name, type_desc from sys.objects
where object_id in (<object_id)> Also I could see a problem of users & roles, as you say you have restored from SQL 2000 and ensure you have copied relevant logins with necessary roles created on SQL 2005. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

http://sqlserver-qa.net/blogs/tools/archive/2007/04/24/sql-server-2005-ad-hoc-updates-to-system-catalogs-are-not-allowed.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

either you lost a table or a stored procedure
(does a view have entries in syscolumns?) find the original db and see what that object_id is

Thanks Mohammed, how can I forgot my own blog[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] DOh. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MohammedU http://sqlserver-qa.net/blogs/tools/archive/2007/04/24/sql-server-2005-ad-hoc-updates-to-system-catalogs-are-not-allowed.aspx</a> MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy. <hr height="1" noshade id="quote"></blockquote id="quote"> Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</center>

It is call buffer overflow [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>