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