Article ID: 834604
Article Last Modified on 3/10/2004
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Service Pack 2
- Microsoft SQL Server 7.0 Service Pack 3
- Microsoft SQL Server 7.0 Service Pack 4
SYMPTOMS
Starting with Microsoft SQL Server 7.0 Service Pack 2 (SP2) or the original released version of Microsoft SQL Server 2000, when a DB-Library application uses the dbtxptr function to retrieve the text pointer for a text or for an image column followed by a call to the dbwritetext function to modify the text or the image column, SQL Server will not recognize the change that is made to the text or the image column by the dbwritetext function. This behavior breaks transactional replication. Any change that is made to the text or the image column of a replicated table at the publisher by using the dbreadtext function or by using the dbwritetext function is not propagated to the subscriber.
The Textcopy.exe sample DB-Library application that is included with SQL Server also exhibits the same problem. See the "TextCopy - Handling text and image data" topic in SQL Server Books Online for the source code for Textcopy.exe.
CAUSE
Starting with SQL Server 7.0 SP2 or the original released version of SQL Server 2000, when you use a DB-Library application that uses a dbtxptr function to retrieve the text pointer, the TEXT_POINTER records are not logged in the transaction log. This change of behavior causes transactional replication to break because transactional replication cannot replicate the text or the image columns that are modified from the DB-Library application. For additional details, see the "More Information" section.
WORKAROUND
To work around this problem, you can change the DB-Library application so that it includes the TEXTPTR intrinsic function together with the selection of the text column.
To illustrate the workaround for the TextCopy.exe sample DB-Library application, modify the Textcopy.cpp file as follows. The main change is in Change #2. Change #2 includes the TEXTPTR intrinsic function with the SELECT statement.
//Change #1:
//Addition of a new integer variable.
int nNumCols = 0; //Number of columns permitted.
//Original: strQuery = "select " + strColumn + " from " + strTable + " " + strWhere;
//Change #2:
//Differentiating between the "IN" and the "OUT" operation modes.
//Including the TEXTPTR intrinsic function with the SELECT statement.
//Modifying the nNumCols value to 2 for the "IN" mode.
//No changes for the OUT mode.
if (bOut)
{
strQuery = "select " + strColumn + " from " + strTable + " " + strWhere;
nNumCols = 1;
}
if (bIn)
{
strQuery = "select " + strColumn + ", TEXTPTR(" + strColumn + ") from " + strTable + " " + strWhere;
nNumCols = 2;
}
//Original: if (dbnumcols (pDbproc) != 1)
//Change #3:
//Using a variable that is named nNumCols instead of a hard coded value of 1.
if (dbnumcols (pDbproc) != nNumCols)
MORE INFORMATION
The DB-Library API is special when it comes to text or to image data manipulation. The following steps explain how the DB-Library works with respect to text or to image columns:
- The standard result set for a text or for an image column value includes the text pointer and the text timestamp information for non-null values. The text pointer and the text timestamp information only occur for text or for image column types if the data is not NULL.
- Whenever you query a text or an image column, you will always receive result set stream information about the text or the image column that includes the text pointer data and the text timestamp data. Remember, this data is per column and per row.
- When you use the dbnextrow or the dbgetrow functions, you have an individual row that is associated with your DBPROCESS structure. At this point, the DB-Library code creates an internal TEXTRECORD structure that is associated with each column in the result set that is a text or an image data type.
- When a DB-Library application calls the dbtxptr function, you must pass the DBPROCESS structure (which has a pointer to the current row after a dbgetrow function call or after a dbnextrow function call), and pass a column number that is associated with the text or the image column in the result set.
- You now have a reference to the text pointer data that was stored in step 3. You can use this text pointer with a dbwritetext function call.
When the server sends a row back to the DB-Library client in addition to the row data the result set for a text or for an image column value includes the text pointer and the text timestamp information for non-null values. This means that when any select occurs on a table that has non-null text or non-null image data, regardless of whether the text column was selected, the text pointer and the text timestamp information is always sent in the row data. When the DB-Library invokes the dbtxptr function call or the dbtimestamp function call, the information is actually retrieved from the row data that is already present on the client without making another round trip to the server. This is specific to the DB-Library API. Therefore, from the server point of view, the server has no idea whether a DB-Library application will want to use the text pointer data that is always sent back in the result stream for purposes of write text or whether the DB-Library application will even use the text pointer data at all.
Other APIs like ODBC do not send the text data together with the row data. The text or the image information is only sent when explicitly requested.
Let us explain this in detail with the following example:
create table t1( c1 int primary key, c2 text ) insert into t1 values( 1, 'hello' ) select c1 from t1 where c2 like '%fish%'
When the “Select c1” statement is executed from a DB-Library application, even though the text column is not selected, SQL Server returns the text pointer and the timestamp information to the DB-Library client in each row data. If the DB-Library application wants to modify the text or the image data, it then calls the dbtxptr function or the dbtimestamp function followed by a call to the dbwritetext function for each row. The dbtxtptr and the dbtimestamp functions retrieve the data from the row that is already present on the DB-Library client. When the server sends the row data and the text or the image data back to the DB-Library client, the server has no way of determining if the select will be followed by a call to the dbwritetext function or the dbupdatetext function.
Pre-SQL Server 7.0 Service Pack 2 behavior
In the original released version of Microsoft SQL Server 7.0 and Microsoft SQL Server 7.0 Service Pack 1 (SP1), whenever a select is performed against a table that has text or image data, the server logs the TEXT_POINTER information in the transaction log. The following IF condition is used to determine when to log the text pointer:
IF (TableIsMarkedForReplication AND TextColumnIsMarkedForReplication)
LogTheTextPointer
As you can see in the example, even though there is no call to the UPDATETEXT Transact-SQL function or to the WRITETEXT Transact-SQL function following the SELECT statement, the TEXT_POINTER is still logged. Therefore, additional unnecessary text or image processing has to be performed by the server. This leads to performance problems when you are dealing with large datasets that contain text or image columns.
Post-SQL Server 7.0 Service Pack 2 or the original released version of SQL Server 2000 behavior
Starting with SQL Server 7.0 SP2 and later or with the original released version of SQL Server 2000, the following IF condition is used to determine when to log the text pointer:
IF (RowStructureContainsTextColumn AND RowStructureMemberVariableHasBeenSetToLogTextPointer)
LogTheTextPointer
Starting with SQL Server 7.0 SP2 or the original released version of SQL Server 2000, to reduce the performance problems with text or with image columns the SQL Server Development team decided that the server would not log the TEXT_POINTER record in the transaction log unless instructed to explicitly. This explicit instruction is in the form of a TEXTPTR intrinsic function. The SQL Server Development team determined that only when the TEXTPTR intrinsic function is invoked that the member variable of the RowStructure is set to log the TEXT_POINTER information in the transaction log. When this change was implemented, the change caused the DB-Library application to break.
Keywords: kbprb KB834604
