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....
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.