locked
ntext and nvarchar(max) RRS feed

  • Question

  • I migrated a database from SQL 2000 to SQL 2008.

    I understand that SQL 2008 will not support nText field type, but it seems that it still working using nText.

    I have nText works in SQL 2000 for years and keep it in new SQL 2008 database.

    I use UPDATETEXT to update the table.

    I just realized that it happens a few times that sometimes UPDATETEXT updates and override previous user update.

    For example, one user update one record at 08:00, and seond user update another record at 10:00.

    The seocnd users update 2 records for the current record and previous user record as well.

    I just want to know any user report this issue and if there is, any work around for this?

    Should I just change the field type to nvarchar(max) and use normal update report to fix this?

    Your help and information is great appreciated,

    Regards,

    Souris,

    Wednesday, August 7, 2013 2:38 PM

Answers

  • The Datatypes Text and NText are deprecated, but still supported in SQL 2008.

    For performance reasons and future support it is advisable to change it to nvachar(max).

    However: The problem you describe does not sound like a bug in ntext handling but rather in the code accessing the data.

    Check the code for the possibility of accessing non-unique data rows. Maybe you can also show the code here.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Proposed as answer by Sofiya Li Friday, August 9, 2013 9:20 AM
    • Marked as answer by Sofiya Li Wednesday, August 14, 2013 1:40 PM
    Wednesday, August 7, 2013 3:05 PM
  • Hmm, your proc is simple, and simple is usually a good sign.

    I suppose ReasonID / MyReview is a primary key or at least has a unique index in the table?

    I would also put the statement inside an explicit transaction. Unfortunately the reasons are not explained, but this may be the reason for your bug, if the TextPointer is not serialized well enough without.

    That’s what BOL says:

    In SQL Server, the in-row text pointer must be used inside a transaction, as shown in the following example.

    CREATE TABLE t1 (c1 int, c2 text)

    EXEC sp_tableoption 't1', 'text in row', 'on'

    INSERT t1 VALUES ('1', 'This is text.')

    GO

    BEGIN TRAN

       DECLARE @ptrval VARBINARY(16)

       SELECT @ptrval = TEXTPTR(c2)

       FROM t1

       WHERE c1 = 1

       READTEXT t1.c2 @ptrval 0 1

    COMMIT

     


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Proposed as answer by Sofiya Li Friday, August 9, 2013 9:20 AM
    • Marked as answer by Sofiya Li Wednesday, August 14, 2013 1:40 PM
    Thursday, August 8, 2013 4:22 PM

All replies

  • The Datatypes Text and NText are deprecated, but still supported in SQL 2008.

    For performance reasons and future support it is advisable to change it to nvachar(max).

    However: The problem you describe does not sound like a bug in ntext handling but rather in the code accessing the data.

    Check the code for the possibility of accessing non-unique data rows. Maybe you can also show the code here.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Proposed as answer by Sofiya Li Friday, August 9, 2013 9:20 AM
    • Marked as answer by Sofiya Li Wednesday, August 14, 2013 1:40 PM
    Wednesday, August 7, 2013 3:05 PM
  • Thanks for the message and help,

    Here is my store proceudre,

    It works for SQL 2000 for many years.

    I got a few reposne that update the wrong record feedback since migrate to SQL 2008.

    Thanks again for helping,

    Regards,

    Souris,

    ALTER PROCEDURE [dbo].[speisUpdateReview]
            @MyReview integer,
     @MyComments NVARCHAR(4000)

           
    AS
      DECLARE @ptrval binary(16)

      SELECT @ptrval = TEXTPTR(ActivityComments)
        FROM tbleisActivityReason
          WHERE ReasonID =  @MyReview

       WRITETEXT tbleisActivityReason.ActivityComments @ptrval @MyComments

    Thursday, August 8, 2013 4:07 PM
  • Hmm, your proc is simple, and simple is usually a good sign.

    I suppose ReasonID / MyReview is a primary key or at least has a unique index in the table?

    I would also put the statement inside an explicit transaction. Unfortunately the reasons are not explained, but this may be the reason for your bug, if the TextPointer is not serialized well enough without.

    That’s what BOL says:

    In SQL Server, the in-row text pointer must be used inside a transaction, as shown in the following example.

    CREATE TABLE t1 (c1 int, c2 text)

    EXEC sp_tableoption 't1', 'text in row', 'on'

    INSERT t1 VALUES ('1', 'This is text.')

    GO

    BEGIN TRAN

       DECLARE @ptrval VARBINARY(16)

       SELECT @ptrval = TEXTPTR(c2)

       FROM t1

       WHERE c1 = 1

       READTEXT t1.c2 @ptrval 0 1

    COMMIT

     


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    • Proposed as answer by Sofiya Li Friday, August 9, 2013 9:20 AM
    • Marked as answer by Sofiya Li Wednesday, August 14, 2013 1:40 PM
    Thursday, August 8, 2013 4:22 PM