CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
misterManager
misterManager

Reputation: 1174

EXECUTE @Var character limit SQL Server 2008 R2

I've been looking all over for an answer to this, but nothing is explicit so hopefully someone knows off the top of their head.

When using EXECUTE with a VARCHAR(MAX) variable, is there a character limit? I've done some experimenting and it seems that when load a variable and then call EXECUTE @var, there is a character limit on what is actually passed through to the server. When calling EXECUTE without the variable and just building the string there, everything appears to work.... is there a reason for this? The error message returned is:

The Name ' (insert around 643 out of 881 characters of my SQL statement variable here) ' is not a valid identifier

Does anybody know anything about why this might be happening? EXECUTE @var fails, EXECUTE 'string' works...

Upvotes: 3

Views: 13609

Answers (2)

Vipeout
Vipeout

Reputation: 76

I thought is was: exec @mystr --assumes the string is a procedure name and is limited to 8000 bytes

exec (@mystr) --has the 2 gigabyte limit.

Upvotes: 6

Lamak
Lamak

Reputation: 70658

In SQL Server 2008, the limit seems to be strings up to 2 GB, according to msdn:

Using EXECUTE with a Character String

In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.

Here is the link with all the information.

If you are having problems, it may be the way you are concatenating the strings on your variable.

Upvotes: 3

Related Questions