>>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.

What is the SQL profiler saying what is executed?