>Hi all
>
>I'm executing a huge SQL string from c# and i seem to be hitting some sort of limit, the SQL consists of 1000's of UNION selects and I get the following error:
>
>Internal Query Processor Error: The query processor ran out of stack space during query optimization
>
>Is there a limit to the size of the SQL you can execute?
>
>Thanks
>Kev

Apply "dividi et impera" to the unions,
this reduce the Max(stackQuerySize):

SELECT * FROM
(SELECT ...
  UNION ... SELECT  
  UNION ... SELECT
 ) T1
UNION ...
SELECT * FROM
(SELECT * FROM
  UNION ... SELECT  
  UNION ... SELECT
 ) T1
UNION ...
SELECT * FROM
(SELECT * FROM
  UNION ... SELECT  
  UNION ... SELECT
 ) T1
UNION ...
SELECT * FROM
(SELECT * FROM
  UNION ... SELECT  
  UNION ... SELECT
 ) T1

try the query engine into Query Analyzer: CTRL+L
( if you see the query Plan, then the stack is sufficient )

If you want a more faster/efficient plannning,
increase the nesting subquery
( remember the subquery's 256 tables limit,
every union nesting add 2 temporary tables,
then with N levels you lose 2^N tables )

SELECT * FROM
(SELECT * FROM
    (       SELECT ...
  UNION ... SELECT  ) T2
  UNION ... SELECT * FROM
    (SELECT ....
    UNION ... SELECT
     ....           ) T2
   ) T1
UNION ... 
SELECT * FROM
(SELECT * FROM
    (       SELECT ...
  UNION ... SELECT  ) T2
  UNION ... SELECT * FROM
    (SELECT ....
    UNION ... SELECT
     ....           ) T2
   ) T1
....

But with this query type, it is easy go into a 701 error: memory insufficient!
Use a temp/var table, or change the query into a join type query.