USE pubs
GO
-- This example shows an attempt to acquire
-- a text pointer without an explicit transaction:
CREATE TABLE t1 (c1 int, c2 text)
EXEC sp_tableoption 't1', 'text in row', 'ON'
INSERT INTO t1 SELECT 1, 'hello there'
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(c2) FROM t1 WHERE c1 = 1
READTEXT t1.c2 @ptrval 0 1
-- Attempting to do the same text pointer assignment
-- and READTEXT inside a user-defined transaction succeeds
BEGIN TRAN
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(c2) FROM t1 WHERE c1 = 1
READTEXT t1.c2 @ptrval 0 1
COMMIT TRAN
GO
-- To see that the shared locks are taken and
-- held automatically, we can execute the
-- same code shown earlier for the text data
-- in the pub_info table, which was not stored
-- in the row itself.
-- If you actually ran that earlier example,
-- the row with pub_id 9999 is now gone,
-- so change the code to look for a row with pub_id 1756. I also must first enable the text in row option for the pub_info table and then use an explicit transaction.
-- WRITETEXT with text-in-row data
EXEC sp_tableoption pub_info, 'text in row', 'ON'
GO
BEGIN TRAN
DECLARE @mytextptr varbinary(16)
SELECT @mytextptr=TEXTPTR(pr_info)
FROM pub_info WHERE pub_id='1756'
WAITFOR DELAY '00:00:20'
-- During the delay, you can attempt to delete
-- this row in another Query Analyzer connection:
-- DELETE FROM pub_info WHERE pub_id='1756'
IF @mytextptr IS NOT NULL
WRITETEXT pub_info.pr_info @mytextptr 'Hello Again'
COMMIT TRAN
GO
-- If the isolation level of the transaction is
-- READ UNCOMMITTED, any text pointer obtained
-- from a table with text in row enabled is
-- considered read-only and cannot be used to update
-- LOB values. They can be used only to read the data.
-- The example below creates a table with in-row text
-- data and then sets the transaction isolation levelt
-- to READ UNCOMMITTED.
-- You can see that the READTEXT succeeds
-- but the WRITETEXT does not.
CREATE TABLE t1 (c1 int, c2 text)
EXEC sp_tableoption 't1', 'text in row', 'ON'
INSERT INTO t1 SELECT 1, 'hello there'
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr=textptr(c2) FROM t1 WHERE c1=1
READTEXT t1.c2 @ptr 0 5
COMMIT TRAN
GO
BEGIN TRAN
DECLARE @ptr varbinary(16)
SELECT @ptr=textptr(c2) FROM t1 WHERE c1=1
WRITETEXT t1.c2 @ptr 'xx'
COMMIT TRAN
GO
