Metadata for computed column does not match column definition

  • 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

  • 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

  • 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.

  • 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.

  • 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.

  • 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

  • Make sure you set the DB to compatibility mode 9.0, Update stats with fullscan,all and update usage!

    That should do it!


    * Noel

  • 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

  • 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