Post reply

Max string length for exec command

  • July 27, 2004 at 4:11 am

    #164561

    Is there a limit for the length of a dynamic sql sring sent to the exec statement?

    e.g. exec ( @mystring )

     I have a statement thus:-

    declare @str1 varchar(8000),@str2 varchar(8000)

    the vars are populated with approx 5000 characters in each and then executed as

    exec(@str1+@str2)

    I don't have any specific error messages as this is executed within a nested procedure in a cursor loop - all I know is this tends to fail and the length of the exec strings is about 10k only in the instance that fails - any help much appreciated.

    ( It's not my code btw <grin> )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 27, 2004 at 5:59 am

    #516264

    There is a limitation and that is 8000.  Straight from BOL:

    Using EXECUTE with a Character String

    Use the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.

    Although each [N] 'tsql_string' or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:

    EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

    Statement(s) inside the EXECUTE statement are not compiled until the EXECUTE statement is executed.

    Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this example, the database context is master:

    USE master EXEC ("USE pubs") SELECT * FROM authors



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 27, 2004 at 6:02 am

    #516267

    cool, what I thought but needed confirmation

    cheers

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 27, 2004 at 8:07 am

    #516303

    actually can anyone clarify if the concatenation of multiple 8k strings works ok ?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 27, 2004 at 9:32 am

    #516340

    It should work with multiple 4k strings, because Microsoft used it: look at the code of the sp_execresultset procedure in master. I guess that EXEC is trying to convert each string to nvarchar before concatenating them and the limit for nvarchar is 4000 characters (even if this means 8000 bytes).

    Razvan

  • July 27, 2004 at 11:57 am

    #516371

    I personally have tried with 4 concatenation of nvarchar(4000) and it WORKS

     


    * Noel

  • July 27, 2004 at 3:41 pm

    #516419

    If you change this to use sp_ExecuteSQL you should no longer have a problem.

    declare @str1 varchar(8000),@str2 varchar(8000)

    exec sp_executesql @str1+@str2

     

    If you look up sp_executesql in SQL BOL you will notice that stmt is defined as ntext. Thus concatenating 2 8000 character strings should work just fine.

     




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • July 27, 2004 at 10:26 pm

    #516458

    Using EXEC (@SQL1+@SQL2...@SQL4), have been able to do up to 32k.  Target of exec has to be in ( ).  Didn't try it but was once told the limit was 64k this way.  Just a reminder, this makes for dynamic SQL and although certainly useful, can be VERY slow depending on what you are doing.

    --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)
    Intro to Tally Tables and Functions

  • July 27, 2004 at 11:46 pm

    #516472

    Gary,

    Using sp_executesql with an expression does not work. Here is a quote from BOL:

    "stmt must be either a Unicode constant or a variable that can be implicitly converted to ntext. More complex Unicode expressions (such as concatenating two strings with the + operator) are not allowed."

    What this means is:

    1. If we want to use a variable, the maximum length is 8000 (the maximum length of a local variable, namely a varchar, which can be implicitly converted to a ntext);

    2. If we want to use a constant, we can use a very long constant ("The size of the string is limited only by available database server memory."), but it must be prefixed with N (to be a Unicode constant).

    Razvan

  • July 28, 2004 at 1:59 am

    #516498

    I've found out that the maximum buffer size ia actually 1Mb or thereabouts based upon 250 x 4000 nvarchar strings. I now wonder if the conversion to nvarchar occurs for each string thus converting a 8000 varchar to 4000 nvarchar, would I loose chars from my varchar ?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • July 29, 2004 at 12:55 am

    #516701

    NVarchar uses two bytes per character.  There is no reason to use it unless there is something "language specific" or the system requires it.

    --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)
    Intro to Tally Tables and Functions

  • July 29, 2004 at 8:33 am

    #516802

    It might be worth contacting SQL Server MVP Erland Sommarskog at esquel@sommarskog.se. He is usually referenced when it comes to dynamic SQL.

    And he is a very friendly person indeed.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 12 posts - 1 through 11 (of 11 total)

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