FIX: Invalid Text Handle Errors May Occur in Transactions

• Microsoft SQL Server, version 6.5

SYMPTOMS

Even if you have the lock isolation level set to HOLDLOCK or REPEATABLE READ, a text pointer can be changed while you are in a transaction. This will result in an invalid text handle when you try to use it again.

For example, during the WAITFOR delay in the following query, another user can update the @mytextptr before you can use it, resulting in an invalid textpointer error:

   BEGIN TRAN
   declare @mytextptr varbinary(16)
   select @mytextptr=TEXTPTR(pr_info)
      from pub_info where pub_id='9999' and
   TEXTVALID('pub_info.pr_info',TEXTPTR(pr_info))=1
   WAITFOR DELAY "00:00:10"
   IF @mytextptr IS NOT NULL
      WRITETEXT  pub_info.pr_info @mytextptr WITH LOG 'Hello Again'
   COMMIT TRAN

WORKAROUND

Use the locking hint UPDLOCK, which causes the textpointer to be correctly held. For example:

   select @mytextptr=TEXTPTR(pr_info)
      from pub_info (UPDLOCK) where pub_id='9999' and
   TEXTVALID('pub_info.pr_info',TEXTPTR(pr_info))=1

STATUS