CodeCloudy

Azure | .Net | JQuery | Javascript | Umbraco

SQL Azure migration: Built-in function ‘TEXTPTR’ is not supported in this version of SQL Server.

on May 15, 2012

Not only ‘TEXTPTR’  also ‘READTEXT’ is not supported in SQL Azure.

You will find this issue when you try to execute an old version of AspState script in sql azure.

You will wonder why sql azure does not support most of the new features. it’s because the sql azure was built without the deprecated features of SQL SERVER 2008 R2.

executing the new version will solve the issue.

you can get the new version here: http://azuresqlsession.codeplex.com/

 

SQL Azure Migration Wizard (SQLAzureMW) is a cool tool that will help us analyze the issues on SQL AZURE migrations.

you can download it from here: http://sqlazuremw.codeplex.com/

Advertisements

6 responses to “SQL Azure migration: Built-in function ‘TEXTPTR’ is not supported in this version of SQL Server.

  1. Nandhini says:

    Hi,

    I’m facing the same issues while migrating my SQL Server database to SQL Azure database.
    TEXTPTR, UPDATETEXT is not supported in SQL Azure database.

    I downloaded the .rar file at http://azuresqlsession.codeplex.com/ but still its unclear to me what that does and how it will fix my issues

    can you please help me to fix this issue?

    kind regards,
    Nandhini

    • kdxshaggy says:

      Hi Nandhini,

      http://azuresqlsession.codeplex.com is only an azure replacement for implementing session state in azure(instead of AspState script).

      i am not sure what kind of schema you are trying to migrate.

      TEXTPTR, UPDATETEXT can exist in any sql queries other than session state scripts. So, azuresqlsession.codeplex.com won’t help on those situations.

      Did you try this tool: http://sqlazuremw.codeplex.com/

      You could use this tool to analyse any errors. the tool is up to date so it should support most of the conversions. the last update was may 4th.

      if this didn’t help, send me a sample piece of code where it doesn’t work. i’ll try to help if possible.

      • Nandhini says:

        Hi Tuan,

        I tried using the tool which can be downloaded from http://sqlazuremw.codeplex.com/ but no use, it is trying to report the error as
        Built-in function ‘TEXTPTR’ is not supported in this version of SQL Server instead of resolving it.

        I’m facing problem in the below Stored Procedure schema, can you pls help me to resolve it.

        CREATE PROCEDURE [dbo].[BH_UpdateImgData]
        (
        @FileID uniqueidentifier,
        @Offset int,
        @Picture image
        )
        AS
        declare @TextPtr binary(16)
        declare @ImgID uniqueidentifier
        declare @ImgData int

        SELECT @TextPtr=TEXTPTR(ImgData), @ImgID = ImgID, @ImgData = DATALENGTH(ImgData)
        FROM ImgTable
        WHERE (ImgID = @FileID)

        if (@ImgID is not null)
        BEGIN
        if (@Offset < @ImgData) AND (@TextPtr is not null)
        BEGIN
        declare @DeleteLength int
        set @DeleteLength = @ImgData-@Offset
        UPDATETEXT ImgTable.ImgData @TextPtr @Offset @DeleteLength
        END
        if (@TextPtr is not null)
        UPDATETEXT ImgTable.ImgData @TextPtr NULL NULL @Picture
        END
        RETURN
        GO

        Many thanks in advance

      • kdxshaggy says:

        I will give you some code samples.

        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

        Instead of of Using TEXTPTR, we can avoid it. and try to use different approaches and methods. the above code can be replaced by following:

        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

      • Nandhini says:

        Hi Tuan,

        Thanks for the above code samples,

        I tried to convert based on my requirement
        UPDATE ImgTable SET ImgData .WRITE(@Picture, NULL, NULL)
        WHERE ImgID = @FileID
        but unfortunately i get error as “Cannot call methods on image.”

        Note: ImgData , @Picture is of datatype Image

      • Nandhini says:

        It also looks like, to update chunk image we need to go for WRITETEXT orUPDATETEXT instead of UPDATE statement, unfortunately SQL AZURE will not support WRITETEXT or UPDATETEXT for Image functions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: