SQL Azure Prep: Part 3

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.

{6230289B-5BEE-409e-932A-2F01FA407A92}