I don't know the solution to your problem, I just wanted to say -
I feel your pain. I just ran into this problem today. Luckily, I was able to reduce my dynamic query down to within 4000 characters because I'm building an xml script.

I will let you know if I find a solution to this.

-------------- Original message --------------
From: "Paul Bradshaw " <mssqldba-ezmlmshield-x46765062.[Email address protected]

> I need to build a dynamic string of SQL that can be very long (it's
> actually creating a view dynamically based on values in a table). The
> problem is, this SQL can very easily go over 8000 characters that a
> varchar variable can hold.
>
> How can I dynamically build up a long sql statement in a string variable
> and EXEC it, if it's possible the string value will be longer than 8000
> characters?
>
> I can't declare local variables of type text. The solution has to work
> on both SQL2000 and SQL2005. I'm at a loss here. Help?
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>

MS Sql Server LazyDBA home page