October 4, 2007 at 2:37 am
use yourdb;
-- Based on (class=0,object_id=27199197,column_id=0,referenced_major_id=11199140
select object_name( 11199140 ) as ToBeRevised
, object_name( 27199197 ) as ToBeRevisedToo
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 4, 2007 at 3:01 am
Excellent thanks, got it all working now
January 24, 2008 at 8:19 am
We have the same issue with three databases having reference to two tables with several Column IDs that are in SysDepends, but the columns are no longer in SysColumns. To resolve this, we only needed to drop/re-create the stored procedures. This fixed the bad dependencies. The missing dependencies did exist in SQL 2000, but the DBCC CheckCatalog in 2005 showed the errors.
Hope this helps.
February 11, 2008 at 9:09 am
Guys,
I had this issue not so long ago so I wrote this procedure which recompiles all of the stored procedures that the DBCC CHECKDB is complaining about, its probably no the greatest way of doing it but hey, it worked for me!! 😀
I know I should've added this to the scripts section but I can't download the text editor thing from the office.
Let me know how you get on.
Andy
-- =====================================================================
-- Name:spFixInconsistencies
-- Author:Andy Elsmore
-- Create date: 2008-01-03
-- Description:Procedure to fix Check Catalog error 8992 inconsistency
--errors after restoring a database to SQL2005 from
--a SQL2000 backup
-- =====================================================================
CREATE PROCEDURE spFixInconsistencies
AS
BEGIN
SET NOCOUNT ON;
-- Only run if >= SQL2005
DECLARE @sSQLVersion char(2);
SELECT @sSQLVersion = SUBSTRING(@@VERSION, CHARINDEX('-', @@VERSION) + 2, 1);
IF @sSQLVersion != 9
BEGIN
RAISERROR(N'SQL version must be at least 2005.', 16, 1);
RETURN;
END
DECLARE @DBName varchar(30);
SET @DBName = DB_NAME();
IF OBJECT_ID('tempdb..#CheckDBTemp', N'U') IS NOT NULL
DROP TABLE #CheckDBTemp;
CREATE TABLE #CheckDBTemp
(
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Error]int,
[Level]int,
[State]int,
[MessageText]varchar(8000),
[RepairLevel]int,
[Status]int,
[DbId]int,
[ObjectId]int,
[IndexId]int,
[PartitionId]int,
[AllocUnitId]int,
[File]int,
[Page]int,
[Slot]int,
[RefFile]int,
[RefPage]int,
[RefSlot]int,
[Allocation]int
);
INSERT INTO #CheckDBTemp
EXEC ('DBCC checkdb(''' + @DBName + ''') WITH NO_INFOMSGS, TABLERESULTS');
DELETE FROM #CheckDBTemp WHERE [Error] <> 8992;
DECLARE @Count int;
SELECT @Count = COUNT(ID) FROM #CheckDBTemp;
IF @Count <= 0
PRINT 'Process complete -- No consistency errors found'
ELSE
BEGIN
DECLARE @ID int, @Text varchar(8000);
DECLARE msg_cursor CURSOR
FOR
SELECT [ID], [MessageText] FROM #CheckDBTemp;
OPEN msg_cursor;
FETCH NEXT FROM msg_cursor INTO @ID, @Text;
WHILE @@fetch_status <> -1
BEGIN
DECLARE @Start int, @End int
/* ----------------- Get the main bit we need ------------------ */
SET @Start = CHARINDEX('of row', @Text) + 8;
SET @End = CHARINDEX(')', @Text, @Start) - @Start;
SET @Text = SUBSTRING(@Text, @Start, @End);
UPDATE #CheckDBTemp
SET [MessageText] = @Text
WHERE ID = @ID;
/* --------------------------------------------------------------*/
/* ------------ Get the start and end of Object_Id ------------- */
SET @Start = CHARINDEX('object_id=', @Text) + 10
SET @End = CHARINDEX(',', @Text, @Start) - @Start
UPDATE #CheckDBTemp
SET [ObjectId] = SUBSTRING(@Text, @Start, @End)
WHERE ID = @ID;
/* --------------------------------------------------------------*/
FETCH NEXT FROM msg_cursor INTO @ID, @Text;
END
CLOSE msg_cursor;
DEALLOCATE msg_cursor;
/* ------------ Lets recreate all those procedures ------------- */
DECLARE @ObjectID int;
DECLARE sp_cursor CURSOR
FOR
SELECT [ObjectID] FROM #CheckDBTemp;
OPEN sp_cursor;
FETCH NEXT FROM sp_cursor INTO @ObjectID;
WHILE @@fetch_status <> -1
BEGIN
DECLARE @Proc varchar(max)
SET @Proc = ''
SELECT @Proc = @Proc + sys.syscomments.text
FROM sys.syscomments
INNER JOIN sys.sysobjects ON sys.syscomments.id = sys.sysobjects.id
WHERE sys.syscomments.id = @ObjectID;
SELECT @Proc = REPLACE(@Proc, 'CREATE PROCEDURE', 'ALTER PROCEDURE');
EXEC (@Proc)
FETCH NEXT FROM sp_cursor INTO @ObjectID;
END
CLOSE sp_cursor;
DEALLOCATE sp_cursor;
DROP TABLE #CheckDBTemp;
PRINT 'Process complete -- ' + LTRIM(STR(@Count)) + ' consistency errors corrected'
END
SET NOCOUNT OFF;
END
GO
-- =====================================================================
February 11, 2008 at 12:21 pm
Hi Andy,
Thank you for the script.
It's this kind of "simple" scripts that make this site such a great resource !
Can you post it in the contribution center ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2008 at 9:25 am
Hi All,
I just wanted to post my own experience with this. I do restore a SQL 2000 backup to SQL 2005 SP2 and then have this same error. After much work, both on the SQL 2005 server and internet searching, I was led to this site and this topic. In my case, the syscolumns reference was to a procedure that no longer existed. So my fix was to update the syscolumns table on the SQL 2000 server and remove that reference. Then I made a fresh backup and restored that one to my SQL 2005. Now no more problem with SQL 2005.
regards,
JD
November 10, 2008 at 3:52 pm
I'll gladly move this along.
Running a dbcc checkcatalog after a dbcc checkdb gives me the following error:
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=1858417990) of row
(object_id=1874418047) in sys.objects does not have a matching row (
object_id=1858417990) in sys.objects.
Running the query against sysobjects:
select object_id, name, type_desc from sys.objects
where
object_id in (1858417990, 1874418047)
Results:
1874418047OrganizationUDF_PKPRIMARY_KEY_CONSTRAINT
Site may not have a backup to restore to (wtf?).
SQL Server 2005 SP2 Standard Edition.
Database created 8/10/2007
Any thoughts on how to possible resolve this?
November 10, 2008 at 3:59 pm
Sorry, didn't see the additional reply pages. I'll try recompiling the SP's and see where that takes me.
December 29, 2008 at 1:09 pm
Just to complete the topic, Old Hands was right (proved on a similar case): DBCC CHECK DB found references to non-existent columns in some legacy stored procedures. After these procedures were dropped or corrected the errors disappeared.
Now my question is: why in my case didn't it complain earlier? Indeed, the database was migrated (via backup-restore) from SQL 2000 about six months ago; neither table structure nor those old stored procedures were changed since then. However, the consistency errors (Check Catalog Msg 3853) showed up only now.
Thanks,
--yul
Yul Wasserman
January 2, 2009 at 8:42 am
Hi Yul,
While I don't have an answer for your question, I thinking that maybe a post SP2 fix brought the issue to the surface.
I wanted to follow up on my last post regarding checking SP's:
I checked and the DBCC issue was with a system table and not with a SP where we had a PK record but the record for the corresponding table was missing. The PK record referred back to the missing table record and was causing the error.
Most stuff we have read said a restore was the only option.
Days had passed since the customer discovered the problem and they didn't have a viable backup to restore back to.
One of our DBA's found information in the following link by Paul S. Randal which
we worked through (with extreme caution as noted in the article) and were able to resolve the DBCC error.
The record we modified was not needed by our program so modifying it did not cause
an issue with the program. Hope this helps others.
Thanks,
Dave
January 2, 2009 at 10:17 am
- do you have "torn page detection" set to ON for all databases ?
- do you perform dbcc checkdatabase on a regular basis ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 4, 2009 at 6:31 am
Hello,
I had the same problem, msg 8992. Some views and stored procedures of a database were trying to reference to a no more existing field of a table. After finding the errors with checkdb I really didn't know how to solve the problem... so thanks a lot for sharing!
sb
August 27, 2009 at 10:54 am
All:
I've had this issue before. It has occured with databases that I migrate from SQL Server 2000 to SQL Server 2005.
To resolve the issue, you need to find and fix the broken dependency. The last time I ran into this, I received the following message:
"Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (referenced_major_id=826798353,referenced_minor_id=1) of row (class=0,object_id=172228064,column_id=0,referenced_major_id=826798353,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=826798353,column_id=1) in sys.columns."
I ran the following statement to determine the name of the referenced (independent) object.
SELECT object_name(object_id)
, object_name(referenced_major_id)
, *
FROM sys.sql_dependencies
WHERE referenced_major_id = 826798353
AND referenced_minor_id=1
NOTE: Depending on the type of dependent object you may need to tweak this statement. Look up sys.sql_dependencies in BOL to determine what to query.
It turned out to be a function, which I then dropped and recreated (use sp_helptext to get the function's implementation). Once I did that, I was right as rain.
I hope this helps you!
Cheers,
Adam
August 27, 2009 at 11:24 am
Yup, but that's only for errors in sys.sql_dependencies. Fixing this kind of error if it's in sys.objects or any other system table is a massive amount harder.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2009 at 10:07 am
having the same issue when detach/attach databases from 2000 to 2005,
error:
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute
(referenced_major_id=498569310,referenced_minor_id=1) of row (class=0,object_id=235304048,column_id=0,referenced_major_id=498569310,referenced_minor_id=1) in sys.sql_dependencies does not have a matching row (object_id=498569310,column_id=1) in sys.columns.
CHECKDB found 0 allocation errors and 1 consistency
errors not associated with any single object.
I know my source sql2000 databases were clean, so I need to know if this is happened on my new H/W sql2005 or is just a 2000 to 2005 compatibility issue.
any ideas?
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply