Post reply

Displays only the first 8000 charecters.... VARCHAR(MAX)

  • September 1, 2009 at 1:45 pm

    #137852

    Guys,

    In SSMS when I try to execute:

    SELECT CONVERT(VARCHAR(MAX), REPLICATE('a',9000))

    I see only the first 8000 characters displayed. The settings Tool >> Options >> Query Results >> Sql Server >> Results to Grid is set to 65534 and Results to Text is set to 8192.

    Also when I try to run this from SQLCMD

    sqlcmd -S Server -E -y 0 -Q "SELECT CONVERT(VARCHAR(MAX), REPLICATE('a',9000))" -o out.txt

    I see only 8000 charecters.

    The flag -y 0 is supposed to set it up to 1 MB. But I do not more than 8000 characters.

    What could be the problem?

    thanks,

    _UB

    SqlTimes Blog on Sql Server

  • September 1, 2009 at 2:19 pm

    #1047814

    This works......

    SELECT REPLICATE(CONVERT(VARCHAR(MAX), 'a'), 9000)

    REPLICATE must be run under the VARCHAR(MAX) mode to return more characters.

    I knew this, but still did the same mistake. I hope I do not do the same mistake again.

    _UB

    SqlTimes Blog on Sql Server

  • September 1, 2009 at 2:46 pm

    #1047837

    Thanks for posting back after you found the solution to your question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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