Microsoft KB Archive/114571

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

Q114571

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.2
Microsoft SQL Server version 4.2x

BUG# OS/2: 1848 (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.

For example, the following table and trigger are created:

  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

Inserting a row with the text column containing more than 1800 bytes will fail with the error 7113.

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.

Additional query words: Windows NT

Keywords : kbprogramming
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbZNotKeyword3 kbSQLServ420OS2