>It works if I change the query so that it creates ##Temp1
>
>I've only seen this happen when there are Date fields in the WHERE clause.
>
>I tried hard coding date values and got the same problem. I also tried using ? instead of the angle brackets without
>getting a better result.
>
>We're puzzled about why the extra # makes such a difference and any information you can provide
>will be a big help.

The ## prefixed tables stay for the duration of your handle; the # prefixed stay for the duration of your current batch, which is a tad harder to define - generally, when you need them again, you need to create them again; when your batch failed and you run it again, they are still there and you have to drop them first.

Generally, this should work:

TEXT TO lcSql TEXTMERGE NOSHOW PRETEXT 15
  SELECT fieldlist
    INTO #temp1
    FROM table JOIN table ON condition
         etc
    WHERE datefield BETWEEN <<date1>> AND <<date2>>

  SELECT fieldlist
    INTO #temp2
    FROM #temp1 JOIN table ON condition
         etc
    WHERE datefield BETWEEN <<date1>> AND <<date2>>
-- more should go here, select what you want from these temp tables

drop table #temp1
drop table #temp2

ENDTEXT

If you send all this as one command, then your #tables should stay alive for the duration of that command. You better drop them in the end, just in case.