Post reply

Length greater than Nvarchar(max)--Need help

  • September 23, 2010 at 5:04 am

    #226748

    Hello,

    We are facing the problem that

    one of the table field length more than Nvarchar(max).

    We couldnt take that full length,if we tried following query also

    "declare @sql_txt varchar(max)

    set @sql_txt = '.........need to get this string...... '

    print(@sql_txt)

    if (len(@sql_txt) > 8000)

    print(substring(@sql_txt, 8001, len(@sql_txt) - 8000))

    PRINT '> 8000'

    if (len(@sql_txt) > 16000)

    print(substring(@sql_txt, 16001, len(@sql_txt) - 16000))

    PRINT '> 16000'

    if (len(@sql_txt) > 24000)

    print(substring(@sql_txt, 24001, len(@sql_txt) - 24000))

    PRINT '> 24000'

    if (len(@sql_txt) > 32000)

    print(substring(@sql_txt, 32001, len(@sql_txt) - 32000))

    PRINT '> 32000'"

    we are not able to get the full length.

    How can we take the string if greater than Nvarchar(max).

    Give me the suggestion on this.

    Thanks,

    ARP

  • September 23, 2010 at 5:11 am

    #1226217

    What is the data type of this column which is causing problems?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 23, 2010 at 5:12 am

    #1226220

    Hi.

    The datatype for that column Nvarchar(Max)

    Thanks

  • September 23, 2010 at 5:17 am

    #1226222

    antonyp 46888 (9/23/2010)

    Hi.

    The datatype for that column Nvarchar(Max)

    Thanks

    It's not at all clear what you are trying to do.

    Are you attempting to insert a string into a Nvarchar(Max) column?

    How are you doing this?

    Do you need nvarchar rather than varchar?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 23, 2010 at 5:43 am

    #1226231

    antonyp 46888 (9/23/2010)

    We are facing the problem that

    one of the table field length more than Nvarchar(max).

    nVarchar(max) can store over 1 billion characters in it. That's 2 GB of data.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 23, 2010 at 6:00 am

    #1226235

    Better to use 'Text' DataType whenever we need to store large texts in our database.

  • September 23, 2010 at 6:03 am

    #1226237

    wisdom.vivek (9/23/2010)

    Better to use 'Text' DataType whenever we need to store large texts in our database.

    BOL:

    ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    Careful what you suggest. 🙂

    -- Cory

  • September 23, 2010 at 6:03 am

    #1226238

    wisdom.vivek (9/23/2010)

    Better to use 'Text' DataType whenever we need to store large texts in our database.

    Please explain why, in a SQL Server 2008 forum section?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 23, 2010 at 6:04 am

    #1226240

    wisdom.vivek (9/23/2010)

    Better to use 'Text' DataType whenever we need to store large texts in our database.

    no, the TEXT datatype is in the process of being deprecated in favor of the varchar(max)/nvarchar(max) data types. the current MS recommendation is to migrate existing code to no longer use the TEXT datatype, which may be completely dropped in SQL 2011's version, i think?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 23, 2010 at 6:05 am

    #1226242

    you can try using the nvarchar(max) it stores large data or else if you use text

  • September 23, 2010 at 6:19 am

    #1226255

    antonyp 46888 (9/23/2010)

    "declare @sql_txt varchar(max)

    Learner DBA (9/23/2010)

    you can try using the nvarchar(max) it stores large data

    If you read the initial post, you'd see he's already using varchar(max)

    or else if you use text

    From Books Online (as posted by Cory)

    ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply