FIX: Error 644, 5180 Updating Text or Ntext Column with ISNULL (236568)

SYMPTOMS

When updating a text column with a ntext field (or updating a ntext column with a text field) from another table with the ISNULL operator, error 644 or 5180 occurs as follows:

If the ntext (or text) column contains a NULL value and a primary key exists on the table being updated, error 644 occurs.

-or-
If the updated table does not contain a primary key, the same update generates error 5180.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

For example, if t1.b is a text datatype and t2.b is a ntext datatype. If t2.b contains a NULL value the following UPDATE statement fails with error 5180:

Update t1
Set b = IsNull(t2.b, "abc")
From t2

Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 8084 in database 'pubs'. Table or database may be corrupted.

When you create a primary key on the table t1, the same UPDATE statement generates error 644:

Server: Msg 644, Level 21, State 1, Line 1
Could not find the index entry for RID '16100acdf1c12' in index page (1:189), index ID 0, database 'pubs'.