April 21, 2008 at 12:47 pm
I have a sql 2005 database that I backed up from my laptop, and restored on a client's sql server. (Both database are sql 2005). Everything appears to work fine, but I get the following error message:
A number of tables that have computed columns return the message "Metadata stored on disk for computed column 'xxx' in table 'dbo.zzz' did not match the column definition. In order to avoid possible index corruption, please drop and recreate this computed column."
I realize I could drop and recreate the computed columns, as the message suggests. I am just wondering if there is another way to refresh the metadata without individually dropping and recreating all of these computed columns.
Any help greatly appreciated.
Thanks & Regards
shashi kant chauhan
April 28, 2008 at 10:05 pm
Hi, So far i have not recived any expert suggestion of my problem.
what should i do when my computed column have some relation with other tables column
Regards
shashi kant chauhan
May 5, 2008 at 12:21 pm
I have the same problem that has just cropped up on a table.
My column is simply taking Lastname + ', ' + firstname. No idea what to do.
FWIW..I did drop the column and rebuild it and I still get the message.
January 15, 2009 at 4:00 am
I'm having this problem with one of my LCS tables, dbo.HomeResourcePermission. It's in my rtc database, which was migrated from sql 2000 to 2005, but is still running in sql 8.0 compatibility mode.
February 18, 2009 at 6:46 am
try alter table..alter column and then rebuild the indexes if you have any on that column...this should fix.
OR
drop and re-create the column.....which is much safer as it is suggested by MS.
February 18, 2009 at 8:08 am
A number of tables that have computed columns return the message "Metadata stored on disk for computed column 'xxx' in table 'dbo.zzz' did not match the column definition. In order to avoid possible index corruption, please drop and recreate this computed column."
When did you first see this error occurring? Was it right when you built the computed column? Or was it after installing any service packs or hotfixes?
Also, just out of curiosity, what are the datatypes of the columns that you're using for your computed column?
Can you post the column names & datatypes as well as the computed column formula?
Regards, Jim C
February 18, 2009 at 11:06 am
Make sure you set the DB to compatibility mode 9.0, Update stats with fullscan,all and update usage!
That should do it!
* Noel
October 27, 2009 at 3:33 pm
For those of you that want it..this script worked for me. I then reindexed all tables affected after I ran this script.
Set NoCount On
Go
Print '--Drop Columns'
SELECT 'Alter Table ' + OBJECT_NAME(Col.OBJECT_ID) + CHAR(10) +
' Drop Column ' + Col.name + CHAR(10) +
'Go'
FROM sys.columns AS Col
Inner Join SysComments Comments ON
Col.object_id = Comments.id And
Col.column_id = Comments.number
Where Col.is_computed = 1
ORDER BY Col.OBJECT_ID
Print '--Add Columns Back'
SELECT 'Alter Table ' + Ltrim(Rtrim(OBJECT_NAME(Col.OBJECT_ID))) + CHAR(10) +
' Add ' + Ltrim(Rtrim(Col.name)) + ' As ' + Ltrim(Rtrim(Comments.text)) + CHAR(10) +
'Go'
FROM sys.columns AS Col
Inner Join SysComments Comments ON
Col.object_id = Comments.id And
Col.column_id = Comments.number
Where Col.is_computed = 1
ORDER BY Col.OBJECT_ID
June 28, 2011 at 3:17 am
we had the same error messages while querying table having computed columns but after collecting statistics and update usage no more errors appear in the log.
Regards,
MShenel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply