Post reply

SQL Column nvarchar(max) not accepting all values

  • November 4, 2009 at 6:55 pm

    #227703

    Hi is there a limit on what the concat command can do in SQL 2005. I can use sql query and do a long description of text which is 1000 characters and insert into nvarchar(max).

    This is coming from application and it seems that anything over 1000 it will not insert.

    Anyone had any problems concat...text lines together.

    Any advice on type of column other than nvarchar(max) let me know.

  • November 4, 2009 at 8:20 pm

    #1075121

    you need to make sure every part you concat together are cast as varchar(max); otherwise you hit an 8000 limit(or lower limit if your smallest var is smaller)

    when you don't expect it too:

    here's an example...you'd think that if i add 10 one-thousand char strings together into a max, it'd be 10,000 , but it hits the 8K barrier:

    declare @string varchar(1000)

    set @string = REPLICATE('1234567890',100)

    declare @alltogether varchar(max)

    set @alltogether = @string +@string+@string+@string+@string+@string+@string+@string+@string+@string --10 times, you'dthink 10,000 right

    select datalength(@alltogether)--only 8000!

    set @alltogether = CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)+CONVERT(varchar(max),@string)

    select datalength(@alltogether)--whoopee! 10,000

    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!

  • November 5, 2009 at 11:26 am

    #1075526

    Thanks for the example.

Viewing 3 posts - 1 through 2 (of 2 total)

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