Transactional replication for text or for image data may not work with DB-Library applications that use the dbtxptr function in SQL Server 7.0 Service Pack 2 or in SQL Server 2000 (834604)

//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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.