Working from memory here, but I seem to recall that you can concatenate
multiple strings in one EXECUTE. For example:

EXECUTE(@q1 + @q2 + @q3 ...)

This can get a bit cumbersome, but it overcomes the 8000-byte limit.





-----Original Message-----
From: Paul Bradshaw
[mailto:mssqldba-ezmlmshield-x46765062.[Email address protected]
Sent: Tuesday, January 30, 2007 11:00 am
To: LazyDBA Discussion
Subject: Need to exec long SQL string

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