BUG: Insert Text w/ More Than 1800 Bytes May Cause Error 7113

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

       NT:  750 (4.2)

SYMPTOMS

Inserting into a table with a text column of more than 1800 characters, which fires a trigger containing a delete followed by an insert on the same table, causes error 7113:

   Msg 7113, Level 22, State 1

   Datasize mismatch detected when transferring TEXT/IMAGE value. First
   fragment page number XXX. The SQL Server is terminating this process.

  CREATE TABLE test ( col1 int, col2 text )
  GO

  CREATE TRIGGER test_ins ON test FOR INSERT  AS
  BEGIN
         DELETE test
         FROM test, inserted
         WHERE test.col1 = inserted.col1

         INSERT test
             SELECT *
             FROM inserted
  END

WORKAROUND

To workaround this problem, swap the order of the DELETE and INSERT clauses in the trigger. Depending on the situation, you may need to add or modify the WHERE clauses to make sure that swapping the order does not affect the correctness of the query.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Microsoft SQL Server version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.