FIX: Transact-SQL READTEXT with INSERT EXEC Generates Error 611

• Microsoft SQL Server version 6.5

SYMPTOMS

With SQL Server 6.5, you can create a stored procedure that uses the READTEXT Transact-SQL function to return portions of a text or image column as a result set. The server also allows the Transact-SQL INSERT EXEC functionality to directly insert a result set from a stored procedure into a table.

However, when the stored procedure fails to explicitly call the Transact- SQL return function, error 611 occurs:

   Msg 611, Level 21, State 1
   Attempt made to end a transaction that is idle or in the middle of an
   update.
   The SQL Server is terminating this process.

WORKAROUND

To work around this problem, add an explicit return 0 statement to the end of the stored procedure.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in the latest U.S. Service Pack for SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The following is a sample Transact-SQL script that uses the READTEXT and INSERT EXEC functionality:

   use pubs
   go

   -----------------
   drop procedure spTest
   go

   -----------------
   create procedure spTest
   as
   declare @txtPtr varbinary(16)

   select @txtPtr = TEXTPTR(pr_info)
   from pub_info
   where pub_id = '0736'


   READTEXT pub_info.pr_info @txtPtr 0 255
   go


   -----------------
   drop table tblTest
   go

   -----------------
   create table tblTest
   (
   tData text
   )
   go

   -----------------
   select * from tblTest
   go

   -----------------
   insert into tblTest
   exec spTest
   go

   -----------------
   select * from tblTest
   go