writetext

Description

Permits minimally logged, interactive updating of an existing text, unitext or image column.

Syntax

writetext [[database.]owner.]table_name.column_name
	text_pointer [readpast] [with log] data

Parameters

table_name.column_name

is the name of the table and text, unitext or image column to update. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

text_pointer

a varbinary (16) value that stores the pointer to the text, unitext or image data. Use the textptr function to determine this value. text, unitext or image data is not stored in the same set of linked pages as other table columns. It is stored in a separate set of linked pages. A pointer to the actual location is stored with the data; textptr returns this pointer.

readpast

specifies that the command should modify only unlocked rows. If the writetext command finds locked rows, it skips them, rather than waiting for the locks to be released.

with log

logs the inserted text, unitext or image data. The use of this option aids media recovery, but logging large blocks of data quickly increases the size of the transaction log, so make sure that the transaction log resides on a separate database device. See create database, sp_logdevice, and the System Administration Guide for details.

data

is the data to write into the text, unitext or image column. text and unitext data must be enclosed in quotes. image data must be preceded by “0x”. Check the information about the client software you are using to determine the maximum length of text, unitext or image data that can be accommodated by the client.

Examples

Example 1

Puts the text pointer into the local variable @val. Then, writetext places the text string “hello world” into the text field pointed to by @val:

declare @val varbinary (16) 
select @val = textptr (copy) from blurbs 
    where au_id = "409-56-7008" 
writetext blurbs.copy @val with log "hello world"

Example 2

declare @val varbinary (16) 
select @val = textptr (copy) 
from blurbs readpast
    where au_id = "409-56-7008" 
writetext blurbs.copy @val readpast with log "hello world"

Example 3

writetext includes information about unitext datatypes, and places the string “Hello world” into the unitext field that @val points to:

declare @val varbinary (16) 
select @val = textptr (ut) from unitable 
where i = 100 
writetext unitable.ut @val with log "Hello world"

The varchar constant is implicitly converted to unitext before the column is updated.

Usage


Using the readpast option

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must be the table owner or a user with update permission to run writetext.

See also

Commands readtext

Datatypes Converting text and image datatypes