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}

Comments

  1. I am so glad that in the year 2010 I was able to transport a 15yrear old databaase on SQL Server 2005 backwards in time from 2020 so you write this blog!

    - Dr. Who and the Tardis Transport Company

    ReplyDelete
  2. Ok, I laughed. My intent was to say that the database was 15 years old, not the SQL Server version it was running on. I acutally started this project on SQL Server 2000, however upgraded once since then. I also didn't want to say that I was upgrading from SQL Server 2000 to SQL Azure. On one hand the database really is SQL Server 2000 because it was running at level 70.

    ReplyDelete
  3. Thanks for this - I know its an old post but I have just been doing a similar exercise, moving to Azure, and fell over the same issue. We resolved by reviewing the database for all fields of type text and updated to nvarchar(max) - perhaps only manageable with a smaller database but this may help others.

    ReplyDelete

Post a Comment

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

How to convert SVG data to a Png Image file Using InkScape

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation