FIX: READTEXT May Fail w/ Msg 7134 If Hard Coding Text Pointer (170507)

SYMPTOMS

The READTEXT statement will fail with "Msg 7134, Level 16, State 2" if both of the following conditions are true:

The text pointer provided is hard coded.

-and-
The READTEXT statement is the first statement of the batch.

WORKAROUND

To work around the problem, do either of the following:

Use a local variable to store the text pointer, instead of directly hard coding the text pointer value.

-or-
Make sure that the READTEXT statement is not the first statement in the batch. You can consider doing a "dummy" select, such as "select @@version".

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 3. This problem was corrected in the latest Microsoft SQL Server 6.5 U.S. Service Pack. For information on obtaining the service pack, query on the following word in the Microsoft Knowledge Base (without the spaces):

   S E R V P A C K

MORE INFORMATION

The following code segment demonstrates the problem:

use pubs
go
create table test (Tvalue text)
go
insert into test values('This is a test')
go

select textptr(Tvalue) from test
go

-- To demonstrate the problem, the following hard coded text pointer
-- value should be based on the result of the previous select:

readtext test.Tvalue 0x510100000000000001000000aa100000 0
0
go

--
-- The following error message is displayed:
--    Msg 7134, Level 16, State 2
--
-- The following code segment works fine:

declare @txtptr  varbinary(16)
select @txtptr=textptr(Tvalue) from test
readtext test.Tvalue @txtptr 0 0
go

--
-- READTEXT only fails if it is the first statement of the batch.
-- The following code segment also works:

select * from titles where title_id = 'PS7777'
readtext test.Tvalue 0x510100000000000001000000aa100000 0
0
go