Chapter 1: Commands

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

The maximum length of text that can be inserted interactively with writetext is approximately 120K bytes for text, unitext or image data.
By default, writetext is a minimally logged operation; only page allocations and deallocations are logged, but the text, unitext or image data is not logged when it is written into the database. To use writetext in its default, minimally logged state, a System Administrator must use sp_dboption to set select into/bulkcopy/pllsort to true.
writetext updates text data in an existing row. The update completely replaces all of the existing text.
writetext operations are not caught by an insert or update trigger.
writetext requires a valid text pointer to the text, unitext or image column. For a valid text pointer to exist, a text, or unitext column must contain either actual data or a null value that has been explicitly entered with update.

Given the table textnull with columns textid and x, where x is a text column that permits nulls, this update sets all the text values to NULL and assigns a valid text pointer in the text column:
```
update textnull 
set x = null
```
No text pointer results from an insert of an explicit null:
```
insert textnull values (2,null)
```
And, no text pointer results from an insert of an implicit null:
```
insert textnull (textid) 
values (2)
```
insert and update on text columns are logged operations.
You cannot use writetext on text and image columns in views.
If you attempt to use writetext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message is generated, instructing you to run dbcc fix_text on the table.
writetext in its default, non-logged mode runs more slowly while a dump database is taking place.
The Client-Library functions dbwritetext and dbmoretext are faster and use less dynamic memory than writetext. These functions can insert up to 2GB of text data.

Using the readpast option

The readpast option applies only to data-only-locked tables. readpast is ignored if it is specified for an allpages-locked table.
If the session-wide isolation level is 3, the readpast option is silently ignored.
If the transaction isolation level for a session is 0, writetext commands using readpast do not issue warning messages. These commands at session isolation level 0 modify the specified text column if the text column is not locked with incompatible locks.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

writetext permission defaults to the table owner, who can transfer it to other users.