>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

Most likely this is caused because the first query runs in the SP_EXECUTESQL stored procedure. The temp table then is created in the scope of the stored procedure and automatically released when it finishes. Most common cause for it to have parameters in the query. Just check the SQL profiler how the query is exactly executed in SQL server.