WRITETEXT (T-SQL)

Permits nonlogged, interactive updating of an existing text, ntext, or image column. This statement completely overwrites any existing data in the column it affects. WRITETEXT cannot be used on text, ntext, and image columns in views.

Syntax

WRITETEXT {table.column text_ptr}
    [WITH LOG] {data}

Arguments

table.column

Is the name of the table and text, ntext, or image column to update. Table and column names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the database name and owner names is optional.

text_ptr

Is a value that stores the pointer to the text, ntext or image data. text_ptr must be binary(16). To create a text pointer, execute an INSERT or UPDATE statement with data that is not NULL for the text, ntext, or image column. For more information about creating a text pointer, see either INSERT or UPDATE.

WITH LOG

Logs the inserted text, ntext, or image data, which aids media recovery. Because text data quickly increases the size of the transaction log, ensure the transaction log has enough space to grow.

data

Is the actual text, ntext or image data to store. data can be a literal or a variable. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 KB for text, ntext, and image data.

Remarks

Use WRITETEXT to replace text, ntext, and image data and UPDATETEXT to modify text, ntext, and image data. UPDATETEXT is more flexible because it changes only a portion of a text, ntext, or image column rather than the entire column.

By default, WRITETEXT is a nonlogged operation. This means text, ntext, or image data is not logged when it is written to the database; therefore, the transaction log does not fill up with the large amounts of data that often make up these data types. To use WRITETEXT in its default, nonlogged state, members of the sysadmin fixed database role must use sp_dboption to set select into/bulkcopy, which allows nonlogged data to be inserted. For more information, see sp_dboption and Setting Database Options.

For WRITETEXT to work properly, the column must already contain a valid text pointer.

Microsoft® SQL Server™ saves space by not initializing text columns when explicit or implicit null values are placed in text columns with INSERT.

The DB-Library dbwritetext and dbmoretext functions and the ODBC SQLPutData function are faster and use less dynamic memory than WRITETEXT. These functions can insert up to 2 gigabytes of text, ntext, or image data.

Caution After using the WRITETEXT statement, execute BACKUP DATABASE. After nonlogged operations occur within a database, the BACKUP LOG statement cannot be performed. For more information, see BACKUP.

To initialize text columns to NULL, use UPDATETEXT when the compatibility level is set to 65. If the compatibility level is set to 70, use WRITETEXT to initialize text columns to NULL; otherwise, UPDATETEXT initializes text columns to an empty string. For more information about compatibility levels, see sp_dbcmptlevel. 

Permissions

WRITETEXT permissions default to those users with SELECT permissions on the specified table. Permissions are transferable when SELECT permissions are transferred.

Examples

This example puts the text pointer into the local variable @ptrval, and then WRITETEXT places the new text string into the row pointed to by @ptrval.

USE pubs

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info)

FROM pub_info pr, publishers p

WHERE p.pub_id = pr.pub_id

    AND p.pub_name = 'New Moon Books'

WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'

GO

See Also