We're trying to create temporary tables in MsSql that can be used in later queries and have come up with something strange.

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

?SQLEXEC(nHandle,lcSql)

This returns 1.

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

?SQLEXEC(nHandle,lcSql)

This generates an error that #temp1 doesn't exist.

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.

Thanks.............Rich