I am running the SQL Azure Migration Wizard v3.2 on my 15 year old SQL Server 2005 database in order to transfer it to SQL Azure. As it ran it outputted these two errors:
UPDATETEXT is not supported in current version of SQL Azure
TEXTPTR is not supported in current version of SQL Azure
When the wizard tried to script this stored procedure:
ALTER PROCEDURE [dbo].[AppendToAreaText] ( @Area_Id int, @Area_Text text ) AS DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(Area_Text) FROM Area WHERE Area_Id = @Area_Id IF (@ptrval IS NULL) UPDATE Area SET Area_Text = @Area_Text WHERE Area_Id = @Area_Id ELSE UPDATETEXT Area.Area_Text @ptrval NULL 0 @Area_Text
The stored procedure sets the Area_Text to the incoming text if the column is currently null or concatenate the text onto the text in the column (saving the all the text) back into the column.
Doing some research and I came upon this MSDN Documentation states: “This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use the large-value data types and the .WRITE clause of the UPDATE statement instead.”
I can replace the legacy code in the stored procedure above with this new stored procedure:
ALTER PROCEDURE [dbo].[AppendToAreaText] ( @Area_Id int, @Area_Text text ) AS UPDATE Area SET Area_Text .WRITE(@Area_Text, NULL, NULL) WHERE Area_Id = @Area_Id
Find all my blog posts about SQL Azure here.