PRB: READTEXT on Text Nullable Columns May Fail at a Replication Subscriber (271853)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q271853 SYMPTOMS
When you execute a READTEXT or WRITETEXT statement on a Text column with an uninitialized Text pointer, you may receive the following error message:
Server: Msg 7133, Level 16, State 1, Line 3
NULL textptr (text, ntext, or image pointer) passed to READTEXT function.
A Text pointer is defined as being uninitialized when its binary value is NULL. You can use the TEXTPT function to determine the binary value of a Text pointer.
CAUSE
In SQL Server 2000, when the replication process applies the initial snapshot to a Subscriber, it replicates all uninitialized (NULL) Text pointers in your Publishing tables as uninitialized (NULL) Text pointers in your Subscribing tables.
This behavior is different from previous SQL Server versions. In SQL Server 7.0, all uninitialized (NULL) pointers are initialized during the initial snapshot process.
This change in behavior in SQL Server 2000 is to enhance initial sychronization performance in replication. The process of intializing Text pointers may be time consuming, especially for large tables. To avoid this performance overhead, SQL Server 2000 does not initialize Text pointers during the initial sychronization process.
This behavior is also true for any subsequent transactions that are replicated to the Subscriber with uninitialized (NULL) Text pointers.
WORKAROUND
Some applications that rely on the SQL Server 7.0 initial synchronization behavior may experience the above problem. There are three ways that you can work around this problem:
- You can use a SELECT statement instead of a READTEXT statement.
- You can use an UPDATE statement to assign any value including NULL to the Text column. This initializes the Text pointer.
- If you are using Snapshot or Transactional replication, you can add the -T 3 parameter to the Run Agent step of the Distribution agent job. This forces the replication process to initialze Text pointers during the initial synchronization process. Note, however, that there is a small performance overhead when you use this parameter.
MORE INFORMATION
This behavior occurs for all types of replication in SQL Server 2000: Snapshot, Transactional, and Merge.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbprb KB271853 |
|---|
|