Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Unanswered: Field too Small; Cannot Use Text or NText; Ugh, little help.

    My Stored Proc runs through a loop and concats the contents of each field into one big nvarchar. Procedure works fine on a smaller scale but now it is being implemented on a very large table and the results of the sequel overflow the nvarchar limits. I looked into using text and ntext but both cannot be declared locally. Does anyone know how I can work aroudn this limitation?

    Summary:The problem is that the temporary variable I am using (nvarchar) is too small to contain the robust size that the SQL is concating into it. The final field it winds up in is a text field and will be able to handle the amount of data, its just getting the data there is the issue..... Your thoughts please....

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What is the maximum number of characters you need?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    10,000-20,000

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Are you on 2000 or 2005? if 2005, you might be able to use nvarchar(MAX)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    While SQL Server 2000 varchar maxes out at 8000 characters, during processing it can handle string of unlimited length. So, for instance, you could have three variables (@A, @B, @C), each storing a full 8000 characters, and you should still be able to concatenate them as long as the destination can support it:
    set @TextColumn = @A + @B + @C.
    So you might try declaring three variables, loading them each as full as they can get, and then concatenating them when they are assigned to the text column.
    It is going to be messy code, though.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2006
    Posts
    5
    Awesome, I will give it a shot and let you know....

  7. #7
    Join Date
    Sep 2006
    Posts
    5
    Using 2003.

    I tried using max, it is not recognized, I am assuming max is a 2k5 addition.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ck6151
    Using 2003.
    I must have missed a version somewhere along the line...
    Yes, varchar(max) is a new datatype in SQL Server 2005.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •