NULL textptr passed to UPDATETEXT function when running deployment
Published 05 June 2013
When running a deployment 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
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 deployment wizard, part of the deployment 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 target 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.