We have a process that generates a unique table name and stores intermediate data in it as a # table. To manage the # table we have two Fox functions:
BackupTempTable()
RestoreTempTable()
The problem is that # table name is always different when these two routines are called. So in a translation to t-sql, dynamic sql needs to be used. This means the sql in our lcSql string must be called through "EXEC @lcSql" or "EXEC sp_executesql @lcSql". The problem there is EXEC uses a unique SPID, so the # table is no longer visible when we come back into the calling environment. We were able to go to a ## table, but coming back from that to a single # table does not work. Here's a code snippet from the routine that fails testing. This is in the Restore routine.
The above routine always returns a 2.
BackupTempTable()
RestoreTempTable()
FoxPro:
text to lcSql textmerge noshow
IF OBJECT_ID('TempDb..#TempTableBackup') IS NOT NULL DROP TABLE #TempTableBackup
select *
into #TempTableBackup && Create a backup of the original temp table and then drop it
from <<This.cTempTable>>
DROP TABLE <<This.cTempTable>>
endtext
This.oConn.ExecuteUpdate(lcSql)
FoxPro:
text to lcSql textmerge noshow
IF OBJECT_ID('TempDb..<<This.cTempTable>>') IS NOT NULL DROP TABLE <<This.cTempTable>>
select *
into <<This.cTempTable>> && Restore the original temp file and drop the backup
from #TempTableBackup
DROP TABLE #TempTableBackup
endtext
This.oConn.ExecuteUpdate(lcSql)
The problem is that # table name is always different when these two routines are called. So in a translation to t-sql, dynamic sql needs to be used. This means the sql in our lcSql string must be called through "EXEC @lcSql" or "EXEC sp_executesql @lcSql". The problem there is EXEC uses a unique SPID, so the # table is no longer visible when we come back into the calling environment. We were able to go to a ## table, but coming back from that to a single # table does not work. Here's a code snippet from the routine that fails testing. This is in the Restore routine.
SQL:
SET @lcSelect = 'SELECT * INTO ' + @tcTempTable + ' FROM ##TempTableBackup '
EXEC sp_executesql @lcSelect -- Create destination from source
IF OBJECT_ID(@tcTempTable) IS NULL -- Destination does not exist, return error
RETURN 2
The above routine always returns a 2.
Last edited: