SQLServerCentral / Discuss Content Posted by James Travis / Article Discussions / Article Discussions by Author / How to Fix Inconsistent Metadata Errors / Latest Posts

RE: How to Fix Inconsistent Metadata Errors

phatal — Tue, 02 Sep 2003 00:51:00 GMT

Thanks for identifying this issue.1 minute to find it via google and 3 minutes to fix it is a good turn around in my book.I agree with an earlier poster that it is probably preferable to fix this problem by renaming the old table and recreating it using 'select * into [new] from [old]' (or similar).Edited by - phatal on 09/02/2003 12:51:24 AMEdited by - phatal on 09/02/2003 12:52:37 AM

RE: How to Fix Inconsistent Metadata Errors

Frank Kalis — Mon, 25 Aug 2003 04:26:00 GMT

Hey, let me explicitely say:Congratulations to your newborn babyI hope all are well ?!?In case this is your first child, it might help you, that in max three years you'll laugh over your situation now. Somehow it turns into something sentimental glorified Take care!Cheers,FrankHere's a sproc written for me by David Burrows

DROP Dirty_NappyGOSELECT Clean_Nappy FROM Nappy_Pile WHERE COUNT(Clean_Nappy) > 0INSERT Baby INTO Clean_NappyGRANT ALL Smiles to BabyGO

Edited by - Frank Kalis on 08/25/2003 04:31:32 AMEdited by - Frank Kalis on 08/25/2003 04:32:11 AM

RE: How to Fix Inconsistent Metadata Errors

Antares686 — Mon, 25 Aug 2003 03:39:00 GMT

Thanks, it means what it means. Sleepless nights, lots of crying, dirty diapers and feeding all the time. But I can't complain I am just the father, mom has it worse than me.

RE: How to Fix Inconsistent Metadata Errors

Frank Kalis — Mon, 25 Aug 2003 00:40:00 GMT

Hi James,

quote:
Sorry Ken I have been out with a new baby and not doing much of anything.

is this some tricky sort of slang or does it mean what it reads Just asking, before I congrat you to something completely different and make even more a fool out of me.Cheers,Frank

RE: How to Fix Inconsistent Metadata Errors

Antares686 — Sat, 23 Aug 2003 10:09:00 GMT

Sorry Ken I have been out with a new baby and not doing much of anything. I have found did run across a reason why my solution will not fix all cases but forgot where I put my notes. I would avoid it at this point and consider going to SP4 which fixed all the test issues I could create. I am still out so responses will be slim.

RE: How to Fix Inconsistent Metadata Errors

ken_opp — Fri, 22 Aug 2003 18:14:00 GMT

Again, two 7.0 SP3 databases both with MDAC 2.7.After removing all indexes, constraints, and even SQL statistics I still got errors when I ran sp_fixColOrder.So my final solution was to rename the table (sp_rename obj, obj_new), create a new table from scratch, and run an "insert into obj select * from obj_old".

RE: How to Fix Inconsistent Metadata Errors

ken_opp — Fri, 22 Aug 2003 09:21:00 GMT

At first I thought this worked, but further testing revealed that is corrupted the table. I have two 7.0 SP3 servers and after running sp_fixColOrder, I was able to read my table remotely. However, when I do a simple table join, I got this error:Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.Now this error message (like most) did not make any sense because I'm doing a simple select and join (no update) yet getting an error indicating that an "insert fails".Next I tried an sp_help on my table on received many errors. Here are a few:Cannot insert the value NULL into column 'index_keys', table 'tempdb.dbo.#spindtab_Cannot insert the value NULL into column 'cnst_type', table 'tempdb.dbo.#spcnsttabI am sure it is the sp_fixColOrder that caused the error, because I recreated the database from a backup and ran my "join query" before and after running sp_fixColOrder.Perhaps the problem is caused by my indexes, default constraints, and/or an identity column.

RE: How to Fix Inconsistent Metadata Errors

ken_opp — Fri, 15 Aug 2003 09:02:00 GMT

Excellent solution. Took me 2 hours to find this article and then 5 more minutes to fix my problem.

RE: How to Fix Inconsistent Metadata Errors

crutherfurd — Fri, 28 Mar 2003 11:19:00 GMT

I can't thank you enough!!! You saved our production run from being late! I ran this on SQL Server 7 sp 4 and it worked like a charm.

RE: How to Fix Inconsistent Metadata Errors

dgm — Thu, 13 Feb 2003 03:28:00 GMT

A new twist......2 servers running SQL7 SP3 and getting this error from a single table. I've manually check the ordinal numbers - they are identical and sequential... there should be no problem? There is, it still gives Msg 7353...I have no explanation, I got round it by locally copying the data I needed into a new table and then querying that.

RE: How to Fix Inconsistent Metadata Errors

fastorro — Tue, 11 Feb 2003 05:09:00 GMT

I was woking in a reply of my real BBDD.Thank's... i have find the solution running instcat.SQLfrom install dir.

RE: How to Fix Inconsistent Metadata Errors

Antares686 — Tue, 11 Feb 2003 04:35:00 GMT

I do hope you made a backup.I did check I still have not had issues so I am wondering what specifically is under the hood that causes this. Can you post your DDL so I can have a look under SQL at what it is doing? As seems to not cause further issues in SP4 I did not test any SP4 machines. I will have the code pulled so no one else can potentially even try this.

RE: How to Fix Inconsistent Metadata Errors

fastorro — Tue, 11 Feb 2003 03:17:00 GMT

NOOOOOOOOOOOOOOOOOOOO!!!my data base has been corrupt due to this solution, This solution DOES NOT WORK with SQL server 7 SP3

RE: How to Fix Inconsistent Metadata Errors

Antares686 — Wed, 09 Oct 2002 03:35:00 GMT

I am not sure why you had this occur. I tested on several machines to insure that I was messing with the proper fields and had no issues with it in any situation I could come up with. What is visually happening to your data and can you post your table structure as I am curious specifically where this becomes an issue to try and improve it. Also, I hope you made a backup of your data beforehand as I would hate to think you lost anything but I did state I make no guarantees as there is always the unknown factor to think about. If you did you may want to restore and try updating the server to SP4 and see if this fixes (please do on a test server to test), when doing the beta it seemed it has a hushed fix of this problem which probably showed other related situations as well. Also was the version an English version server?"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

RE: How to Fix Inconsistent Metadata Errors

thotakura — Tue, 08 Oct 2002 21:09:00 GMT

I ran the script given by James Travis to fix the columnorder . It did fix the column order, but the data in thetable got messed up as the colid and colorder columns are updated in the syscolumns table.Can anybody explain the reason why the data gets messed upas the colid and colorder are updated.Any help is appreciated and can email to sridhar_t @hotmail.comThanksSridhar

RE: How to Fix Inconsistent Metadata Errors

rithomas — Tue, 10 Sep 2002 14:49:00 GMT

If you're worried about changing system columns, a quick workaround to query a linked database would be to query against views instead of tables.

RE: How to Fix Inconsistent Metadata Errors

Antares686 — Thu, 06 Jun 2002 14:25:00 GMT

Thank you, and as for an update, when I was doing SP4 beta it looked like the had casually fixed it there with no mention which is not unusual. However, I have not had a chance to verify with the final release code but will in the next week or so."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

RE: How to Fix Inconsistent Metadata Errors

Argyle — Thu, 06 Jun 2002 09:08:00 GMT

This is so unbelievably on target. I had this exact same problem and MS had no fix for it. THX!!!

RE: How to Fix Inconsistent Metadata Errors

Antares686 — Mon, 25 Feb 2002 19:58:00 GMT

Actually no, the server I tested from was (RC, SP1, SP2) on 2000 and (RC, SP1, SP2, SP3) with SQL 7. The problem actually steams form the column order stored in the syscolumns table and not the string lenght compared to the index which was directly related to SQL 2000 and not 7."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

RE: How to Fix Inconsistent Metadata Errors

darren.green — Mon, 25 Feb 2002 11:48:00 GMT

Is this not covered by this fix-FIX: Error Message 7355 "..Inconsistent metadata for a column" Occurs When You Use a Linked Server (Q306401) http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306401

How to Fix Inconsistent Metadata Errors

Antares686 — Sun, 24 Feb 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/changingcolumn.asp