The maximum length of strings. (Varchar(max)).

  • Generating code / scripts I run into problems with the length of strings.

    I use management studio to generate code / scripts.

    Often the total length of the scripts is longer than 8000.

    But I run in to several problems when my scripts go over 8000.

    The results get chopped off.

    I use the REPLACE funtion a lot. (Is or can the REPLACE be a problem?)

    And I do use Print @string or Select @string to display the result.

    A workaround I have used before is generating the scripts in parts which are under the 8000 characters. But this limits what I can do and is very difficult to generalise.

    Is there a webpage which describes this problem and comes with suggestions ?

    Is there a solution or workaround for this ?

    I thought the varchar(max) would solve this old problem for me, I still hope it will or am I being naive?

    Thanks for any help,

    Ben Brugman

    Some further information:

    I am trying to build a script / code repository.

    Where I can store snippets. (execute,show,print those).

    and use replace to replace parts of the snippets, with other snippets or for example a table name.

    Snippet:

    '

    -- Start_timing_code

    Select count(*) as tel_TTTTTT from TTTTTT

    -- End_timing_code

    '

    In this snippet the TTTTTT can be replaced by a tablename.

    '-- Start_timing_code' can be replaced with a timing code snippet.

    '-- End_timing_code' can be replaced with a timing code snippet, (which does the endtiming).

  • The problem will be the UI, not varchar(max) nor replace().

    SSMS, for example, will cut off at 8k characters, when it's displaying the data.

    Try this:

    USE ProofOfConcept;

    GO

    DECLARE @Cmd NVARCHAR(MAX);

    SET @Cmd = CAST('/*' AS NVARCHAR(MAX)) + REPLICATE(CAST('x' AS NVARCHAR(MAX)),50000) + CAST('*/ print ''Hello World!''' AS NVARCHAR(MAX));

    PRINT @Cmd;

    EXEC sp_executesql @Cmd;

    The Print command will truncate, showing "/*" and then a bunch of "x"s, cut off before the command, but the Hello World! will be printed by executing the string.

    So the string isn't being truncated by anything inside SQL Server. It executes correctly. If it were being truncated, it would generate an error because it would have an unterminated comment block, and that's all it would do. Wouldn't get to Print.

    You can slightly modify how this works by playing with the Options in SSMS (Tools - Options - Query Results), but that only goes so far. It's not really the right tool for dealing with large text strings. You'll want to build an application that is designed for dealing with that kind of thing, or see if someone has already built one that you can use (Bing/Google should find that, if it exists, and you can then decide if one of them will do what you need and want).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (10/8/2012)


    The problem will be the UI, not varchar(max) nor replace().

    SSMS, for example, will cut off at 8k characters, when it's displaying the data.

    Thanks,

    Now I only have to build a print which does do the printing in chuncks.

    I build the code below, this does the printing in chucks of at most.

    Assumed is that linelength does not go beyond a 1000.

    A <CR/LF> is searched between the characters 3000 and 4000.

    The chuck up to that cr/lf is printed. If not found the chunck up to 4000 is printed.

    Thanks again for your advise,

    Ben Brugman

    -- Ben Brugman 20121008

    -- Print @Longstring

    -- Search a new line starting from position 3000

    -- Print up to that position or to a maximum off 4000 characters.

    -- Process the remainder.

    declare @Longstring varchar(max) = 'any long string with crlf between the lines'

    declare @RemainingString varchar(max)

    DECLARE @NL VARCHAR(4)

    declare @plek int = 0

    declare @chunklength int = 4000

    set @NL = '%'+char(13)+char(10)+'%'

    set @RemainingString = @Longstring

    declare @restlengte int = datalength(@RemainingString)

    while DATALENGTH(@RemainingString) > @chunklength

    begin

    -- print '-- restlength = '+convert(varchar(6),@restlengte)

    set @plek = @chunklength

    set @plek = @chunklength-1000+PATINDEX(@NL, substring(@RemainingString,@chunklength-1000,datalength(@RemainingString)))

    IF @plek > @chunklength begin set @plek = @chunklength end

    -- print '-- location found '+ convert(varchar(6),@plek)

    print substring(@RemainingString,1,@plek -2)

    set @restlengte = @restlengte - (@plek+1)

    set @RemainingString = substring(@RemainingString,(@plek+1), datalength(@RemainingString)) -- eindlengte is meer

    end

    print @RemainingString

  • Hi,

    If you cast your VARCHAR(MAX) to XML then SSMS can display the entire contents in an XML editor window.

    DECLARE @Cmd NVARCHAR(MAX);

    SET @Cmd = CAST('/*' AS NVARCHAR(MAX)) + REPLICATE(CAST('x' AS NVARCHAR(MAX)),50000) + CAST('*/ print ''Hello World!''' AS NVARCHAR(MAX));

    SELECT CAST(@Cmd AS XML);

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Artoo22 (10/8/2012)


    Hi,

    If you cast your VARCHAR(MAX) to XML then SSMS can display the entire contents in an XML editor window.

    DECLARE @Cmd NVARCHAR(MAX);

    SET @Cmd = CAST('/*' AS NVARCHAR(MAX)) + REPLICATE(CAST('x' AS NVARCHAR(MAX)),50000) + CAST('*/ print ''Hello World!''' AS NVARCHAR(MAX));

    SELECT CAST(@Cmd AS XML);

    Works if you don't have any special characters in the data. Try it with <, >, &, /, and so on. Can be a problem with code, because a lot of Where clauses (for example), will have less-than and greater-than logic in them.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks Artoo22 and GSquared,

    Tried the XML but this result in error, as described by GSquared. (this looked promising to me).

    If there are no problems with functions and operations like REPLACE, using the 'longprint' routine is sufficient for me.

    Thanx,

    ben

  • ben.brugman (10/8/2012)


    Generating code / scripts I run into problems with the length of strings.

    I use management studio to generate code / scripts.

    Often the total length of the scripts is longer than 8000.

    But I run in to several problems when my scripts go over 8000.

    The results get chopped off.

    I use the REPLACE funtion a lot. (Is or can the REPLACE be a problem?)

    And I do use Print @string or Select @string to display the result.

    A workaround I have used before is generating the scripts in parts which are under the 8000 characters. But this limits what I can do and is very difficult to generalise.

    Is there a webpage which describes this problem and comes with suggestions ?

    Is there a solution or workaround for this ?

    I thought the varchar(max) would solve this old problem for me, I still hope it will or am I being naive?

    Thanks for any help,

    Ben Brugman

    Some further information:

    I am trying to build a script / code repository.

    Where I can store snippets. (execute,show,print those).

    and use replace to replace parts of the snippets, with other snippets or for example a table name.

    Snippet:

    '

    -- Start_timing_code

    Select count(*) as tel_TTTTTT from TTTTTT

    -- End_timing_code

    '

    In this snippet the TTTTTT can be replaced by a tablename.

    '-- Start_timing_code' can be replaced with a timing code snippet.

    '-- End_timing_code' can be replaced with a timing code snippet, (which does the endtiming).

    Why don't you just use sp_helptext and call it a day?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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