Error 2514

Severity Level 16

Message Text

Table Corrupt: Type id %ld (type name = %.*s) do not match between %.*s and %.*s

Explanation

This error occurs when there is a type mismatch between the systypes and the syscolumns system tables. The DBCC CHECKCATALOG statement incorrectly reports type mismatches between systypes and syscolumns using error 2514. It is supposed to report that the usertype column in syscolumns isn't consistent with the usertype column in systypes, but it doesn't print out the offending datatype correctly. Error 2514 often prints out a usertype value that seems out of place (typically 0 or a large number). Usually, the usertype column is either 0 or a number that doesn't appear in systypes.

Action

The following example demonstrates how to find and correct this problem in most circumstances. If your particular example does not match this one, call your primary support provider.

Display the offending rows by executing the following query in the problem database:

select name, type, usertype
from syscolumns
where usertype not in
(select usertype from systypes)

name	type	usertype
-----------------------	----	--------
BugId	56	0
Description	39	175

Examine systypes:

select name, type, usertype from systypes

name	type	usertype
-----------------------	----	--------
binary	45	3
bit	50	16
char	47	1
datetime	61	12
datetimn	111	15
float	62	8
floatn	109	14
image	34	20
int	56	7
intn	38	13
money	60	11
moneyn	110	17
smallint	52	6
sysname	39	18
text	35	19
timestamp	37	80
tinyint	48	5
varbinary	37	4
varchar	39	2

Match the type in step 1 to the type in step 2 to determine what the usertype in syscolumns should be. For example, the type for BugId is 56. Matching the type from the first query with the row from the second query, where type is 56, shows that the usertype should be 7.

If more than one row in systypes contains a matching value for type in syscolumns, choose the corresponding usertype from systypes that has the lowest value. In this example, two rows in systypes have a type of 39, corresponding to usertypes 18 and 2. Therefore, 2 should be the new value for usertype in syscolumns.

For each mismatch, update the corresponding row in syscolumns, as follows:
- Enable updates to system tables:
```
sp_configure 'allow updates', 1
go

reconfigure with override 
go
```
- Update the type column in syscolumns for each type where there is an error. For example:
```
begin tran 
go

update syscolumns set usertype = 7 
where type = 56 
and name = 'BugId' 
and usertype = 0 
go
```
- Check your results carefully and commit the transaction only if the update affects only one row. If it affects more than one row, roll back the transaction. To commit the transaction, use the following:
```
commit tran 
go
```
- Perform updates for any other rows, substituting the required values for usertype, type, and name shown above. Always check that the update affects only one row.
- When you have updated all offending rows, disable updates to system tables:
```
sp_configure 'allow updates', 0 
go

reconfigure with override
go

checkpoint
go
```