October 23, 2003 at 4:16 pm
Using SQL server 2000.
I'm trying to increase the size of a varchar column from 4096 to 6000 in a database with about 3000 rows. I get the following error:
Error: 1780, Severity: 20, State: 0
Could not find column ID 15 in syscolumns for object ID 892582268 in database ID 7
The column is not indexed. The database had been enabled for replication, but there were no published articles. I ran sp_removedbreplication and I still get the same error.
I've run DBCC CHECKDB and CHECKCATALOG on both the user and master databases with no errors reported.
Any ideas?
October 27, 2003 at 8:00 am
This was removed by the editor as SPAM
October 27, 2003 at 1:46 pm
How much is the total size?
October 27, 2003 at 2:14 pm
Possibly an FK referenced or referencing column involved? Is the column you're trying to increase actually column 15 of object 892582268?
Cheers,
- Mark
Cheers,
- Mark
October 27, 2003 at 2:21 pm
Well under 8K - I'm not even able to increase size by 1.
I also posted this in the General forum and a couple of responders thought there might be confusion in the syscolumns table and the way to fix it would be to rebuild the user table I'm trying to change.
I don't mind doing that but it begs the question why can't dbcc discover and repair the confusion in the syscolumns table?
October 27, 2003 at 2:26 pm
quote:
Well under 8K - I'm not even able to increase size by 1.I also posted this in the General forum and a couple of responders thought there might be confusion in the syscolumns table and the way to fix it would be to rebuild the user table I'm trying to change.
I don't mind doing that but it begs the question why can't dbcc discover and repair the confusion in the syscolumns table?
Because the relevant DBCC code is in CHECKCATALOG and that doesn't test this deeply; from BOL:
quote:
DBCC CHECKCATALOG checks that every data type in syscolumns has a matching entry in systypes and that every table and view in sysobjects has at least one column in syscolumns.
--Jonathan
--Jonathan
October 27, 2003 at 3:22 pm
To answer mccork's questions:
1. There is no column referencing involved.
2. The column I want to change has colid=8 in syscolumns. 14 is the last colid...
October 27, 2003 at 3:32 pm
Ok, another shot in the dark.... any chance that there was once 15 columns in the table and that there's an object somewhere that references a now-missing column? Does sp_depends show any such objects?
Cheers,
- Mark
Cheers,
- Mark
October 28, 2003 at 11:56 am
At one time, there were indeed 15 columns - a rowguid column had been created for replication, then deleted in favor of another strategy. But sp_depends does not reveal anything trying to reference that now-missing column.
Jonathan: That's rather distressing news about limitations with dbcc...
October 28, 2003 at 2:37 pm
I am not sure if you will like this idea 🙁
Create a new table with the new column length, move the data there and drop the old table..
October 31, 2003 at 4:42 pm
Curiosity got the better of me and I opened a case with Microsoft. Looked like somebody had mucked with the sysobjects table. I can elaborate, but best to leave it at that...
Thanks to all who responded.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply