>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


In reading the responses I think I wasn't clear enough in my explanation.

I'm pretty sure I know the difference between the # and ## for the temp table name.
(# is available only to the connection which created it
## is available to any active connection
both are removed when the connection which created it is disconnected.)


The wierd thing (from our perspective) is that the single # works when we don't have any date comparisons in the WHERE clause. We've only seen this issue when selecting based on a date value. It doesn't seem to matter if we use textmerge, parameters, BETWEEN rather than < =, > =

Just having the date value seems to mean # doesn't work but ## does.