NULL textptr passed to UPDATETEXT function when running synchronization

Category: Troubleshooting & error messages
Date: 16 Jul 2009
Product: SQL Data Compare
When running a synchronization script produced by SQL Data Compare against an empty database (identical schema but no data), this error may result when attempting to update or insert a row which contains a TEXT or NTEXT column:

NULL textptr (text, ntext, or image pointer) passed to UPDATETEXT function

Please check to see if any WHERE clause is included in the project settings for the table being updated. SQL Data Compare updates text using the UPDATETEXT function, and in order to locate the correct row to update, it selects a TEXTPTR for the row. The problem occurs because the WHERE clause specified to Data Compare is used as the selection criteria for TEXTPTR.

For example, create a project comparing widgetdev to an identical copy of widgetdev's schema, only containing no data. Now apply a where clause to the widgetdescriptions table:

WidgetID IN (SELECT RecordID FROM Widgets)

After running the synchronizatrion wizard part of the synchronization script reads:

DECLARE @pv binary(16)
SELECT @pv=TEXTPTR([Picture]) FROM [dbo].[WidgetDescriptions] WHERE [WidgetID]=2 AND (WidgetID IN (SELECT RecordID FROM Widgets))

If there is currently no data in the widgets table in the destination database, the query will return NULL, resulting in the error.

The solution is to not use the same WHERE clause on both databases. The WHERE clause should only apply to the database on the left in order to work against a blank database.