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