Microsoft KB Archive/271853

= PRB: READTEXT on Text Nullable Columns May Fail at a Replication Subscriber =

Article ID: 271853

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.

Keywords: kbprb KB271853

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.