James,

A parameterised SPT statement is execute on SQL Server using sp_executesql system stored procedure. As result, any temporary table created by such statement is deleted at the end of stored procedure execution. You can use TextMerge to simplify building your queries. You should check for SQLEXEC() return value and in case of error use AERROR() to get detailed info about ODBC error.

...
TEXT TO lcSQL TEXTMERGE NOSHOW
select * into #TempCustNew from Customers where rtrim(ContactTitle) = '<<TitleName>>'
ENDTEXT
lnRet = SQLEXEC(hConn, lcSQL )
IF lnRet = -1
  AERROR(...)
  * Process ODBC error
ENDIF
...

BTW, using 'rtrim(ContactTitle)' prevents query optimizer from using index on ContactTitle column, if there is one.

>I have a problem reading from a SQL temporary table #TempCustNew created by a previous parameterised SPT SQLExec() call. Please see the program below (Set 1).
>
>What confused me is that if #TempCust is created by a non-parameterised SQLExec() called, then #TempCust can be read by a subsequent SQLExec() call. (See Set 2)
>
>In addition, if the 2 queries are combined into one SQLExec() call, it works even with parameterised query when creating #TempCustNew. (See Set 3)
>
>Finally, I tried to create global temporary table ##TempCustGlobal using parameterised SPT SQLExec() and ##TempCustGlobal can be read from a subsequent query. (See Set 4).
>
>Although queries Sets 2-4 work, I really need something like query set 1 for the sake of modularity adn speed. What do I need to do?
>
>Thanks
>James
>
>--------
> function SQLTempTable
>
> local hConn
> local TitleName
> local n
> local aErrorArray
>
> *---* Initialise *---*
>
> * set up the connection
>
> hConn = SQLStringConnect("Driver={SQL Server};Server=MyServer;DATABASE=NorthWind;Trusted_Connection=yes;", .t.)
>
> = SQLSetProp(hConn, 'asynchronous', .F.)
>
> *-- Set 1: using parameterised query, 2nd query cannot read from the temporary table created by the 1st query ---*
>
> TitleName = "Owner"
>
> ? SQLExec(hConn, "select * into #TempCustNew from Customers where rtrim(ContactTitle) = ?TitleName") && return 1
> ? SQLExec(hConn, "select * from #TempCustNew where rtrim(City) = 'Nantes'") && return -1
>
> dimension aErrorArray[1]
> = AERROR(aErrorArray) && Data from most recent error
> ? 'The error provided the following information' && Display message
> FOR n = 1 TO 7 && Display all elements of the array
> ? aErrorArray(n)
> ENDFOR
>
> *-- Set 2: 2nd query can read from the temporary table created by the 1st query ---*
>
> ? SQLExec(hConn, "select * into #TempCust from Customers where rtrim(ContactTitle) = 'Owner'") && OK
> ? SQLExec(hConn, "select * from #TempCust where rtrim(City) = 'Nantes'") && OK
>
> *-- Set 3: using parameterised query, 2nd query cannot read from the temporary table created by the 1st query ---*
>
> ? SQLExec(hConn, "select * into #TempCustNew from Customers where rtrim(ContactTitle) = ?TitleName; " + ;
> "select * from #TempCustNew where rtrim(City) = 'Nantes'", "MyResult") && OK
>
> *-- Set 4: using parameterised query and global temporary table, 2nd query can read from the temporary table created by the 1st query ---*
>
> ? SQLExec(hConn, "select * into ##TempCustGlobal from Customers where rtrim(ContactTitle) = ?TitleName") && OK
> ? SQLExec(hConn, "select * from ##TempCustGlobal where rtrim(City) = 'Nantes'") && OK
>
> *-- Done --*
>
> = SQLDisconnect(hConn)
>
> return
>
> endfunc