Max string length for exec command

Question

Post reply

Max string length for exec command

colin.Leversuch-Roberts

SSC Guru

Points: 52643
More actions
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/

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

cool, what I thought but needed confirmation

cheers

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

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/

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 4

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

noeld SSC Guru Points: 96590 More actions · Answer 5

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

* Noel

gljjr SSChampion Points: 10080 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

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)

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 8

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 9

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/

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

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)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

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]