Error 2514

Severity

Message text

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

Explanation

syscolumns contains a row for every column in a table, and systypes contains a row for every type in a table. Error 2514 occurs when dbcc checkcatalog detects a type mismatch between the systypes and the syscolumns system tables.

Error 2514 is most commonly caused by using the select into command across databases when at least one column from the original table is a user-defined datatype. It can also be caused by cross-database views and applications that map data across databases when a user-defined datatype involved in the operation has conflicting definitions in the two databases.

Error 2514 often prints out a usertype value that seems out of place: 0 (zero) or a large number. If your 2514 error does not match this description, call Sybase Technical Support for further assistance.

Action

Use the following procedure to find the incorrect entry in syscolumns and replace it with the correct value. Note that you actually type “c” and “t”; they are not variables. This method allows Adaptive Server to compare all tables to search for the mismatch.

Identify the rows containing incorrect entries:

1> select c.name, c.usertype, c.type 
2> from syscolumns c
3> where not exists
4> (select * from systypes t
5> where t.usertype = c.usertype)
6> go

   name                      usertype      type
   -------------------       ---------  ------- 
   PartNumber                          0        56

More than one row may be returned.

Search for the correct value for each incorrect syscolumns usertype by querying the systypes usertype column, using the value reported in type. Do this for each row reported above in step 1:
```
1> select t.name, t.usertype, t.type 
2> from systypes t
3> where t.type in ( 56 )
4> and t.usertype < 100 
```
Results of the query should resemble this:
```
 name                   usertype          type
 ---------------    ------------     --------- 
 int                             7              56
```
The value returned under usertype is the correct value that should appear in the usertype column of syscolumns, “7” in this example.
Change the incorrect row value in the systypes column usertype with the correct value that you obtained in step 2:
1. Enable updates to system tables (substitute your site’s values for “7,” “0,” “56” and “PartNumber” in this example query):
```
1> sp_configure "allow updates", 1 
2> go

1> begin transaction
2> update syscolumns set usertype = 7
3> where usertype = 0 and type = 56 
4> and name = "PartNumber"
5> go
```
2. To commit the transaction, type:
```
1> commit transaction
2> go
```
3. Repeat the preceeding steps (a – b) for any other incorrect rows, substituting the required values for usertype, type, and name in the example query.
4. When you have updated all incorrect rows, disable updates to system tables:
```
1> sp_configure "allow updates", 0 
2> go

1> checkpoint
2> go
```
Run dbcc checkcatalog again to verify that the problem is corrected.

Versions in which this error is raised

All versions