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

And what's the content of date1 and date2? If these are dates, they will be output as per your current set("date"), which won't work as date constants need to be strings, surrounded by apostrophes, in TSQL. And even then, I'd rather use something like transform(dtos(date1), "@R 9999-99-99") instead of the date value itself, just so to have the unambiguous value passed to TSQL, and not worry about whether the default date setting in the current instance of SQL server is the same as the one I have in Fox.

So try this instead (mind all the apostrophes in the where clause):

TEXT TO lcSql TEXTMERGE NOSHOW PRETEXT 15
  SELECT fieldlist
    INTO #temp2
    FROM #temp1 JOIN table ON condition
         etc
    WHERE datefield BETWEEN '<<transform(dtos(date1), "@R 9999-99-99")>>' AND '<<transform(dtos(date2), "@R 9999-99-99")>>'
ENDTEXT

?SQLEXEC(nHandle,lcSql)