Hello,

I am attempting to write a stored procedure that builds and executes a
dynamic SQL statement which can be up to 8000 characters long.
Therefore, I have declared a variable of type varchar(8000) which,
according to the documentation, is the maximum acceptable length of
such a variable. Unfortunately, however, SQL Server seems allow
varchars to only be half this size: the resulting string keepings
getting truncated to 4000 characters as reported by the len function.

Is there setting somewhere that would fix this behavior or some
work-around that I can employ that would allow me to execute a dynamic
sql statement that is longer than 4000 characters?

(note: I am not using the sp_executesql proc as it maxes out at 4000; I
am simply calling EXEC which, according to the docs, should be fine)

Thank You.

Chris Moore (CMoore@gmail.com) writes:
> I am attempting to write a stored procedure that builds and executes a
> dynamic SQL statement which can be up to 8000 characters long.
> Therefore, I have declared a variable of type varchar(8000) which,
> according to the documentation, is the maximum acceptable length of
> such a variable. Unfortunately, however, SQL Server seems allow
> varchars to only be half this size: the resulting string keepings
> getting truncated to 4000 characters as reported by the len function.

I don't want to belittle you, but it sounds like you are spelling
"varchar" with a leading "n".

Anyway, without seeing the code, I cannot say much more.

Except that it you are on SQL 2005, you should use varchar(MAX) instead.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx