Unanswered: "Possible schema corruption. Run DBCC CHECKCATALOG." when ALTERing a column
When changing a column from char(6) to varchar(7) via T-SQL:
ALTER TABLE dbo.table_name
ALTER COLUMN existing_column varchar(7) NULL
I recieve the following error message
Msg 211, Level 23, State 7, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have dun various DBCC commands (checkcatalog, checkdb) and neither has reported any issues. I have also run INDEXDEFRAG on the two indexes on this table (1 clustereed, 1 non-clustered - neither are on this column) successfully.
The database in question is a restore from the production database to the development server. I have restored this development database to my local VM and the same command ran successfully.
On the dev environment, @@Version returns:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
As you can see, we are a tad behind on Service Packs - I have requested that the DBAs upgrade and have been waiting over 6 months for this to occur (whole other saga).
My local VM @@Version is:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
So as you can see, the environments to differ, but I don't believe that this is the problem.
Essentially I will need to run this alter command on live release at some point, but if I can't get it to work on test, then there's no way I'll get to run it live...
If you require any more information to assist me, then just ask and I'll see what I can do!
Because of previous incompetent coders - I cannot simply get away with dropping and recreating the column as this affect the column "order" of the table, and there are too many dependant SELECT * scripts that fall over.
Re-create the table and all its constraints looks like my only option; fan-ruddy-tastic!
glad that this DB gets superseeded in the next 6-12 months!