SQL Server temp tables are dropped when the procedure that creates them goes
out of scope or when the session connection ends depending on whether you
use a single '#' or double '##' at the beginning of the tables name. If
your connection to SQL server is contiguous (i.e., the report and the
procedure that created the table both run within the same session
connection) then a session temp table can work.
However, to be on the safe side, just create a standard table with 'tmp'
prefix and set your procedure to drop any existing table with the same name
before you recreate it. You can easily drop and create your temp table
using a SELECT..INTO SQL query like the following example:
Alter Procedure procPrintLienRenewals
AS
DROP TABLE dbo.ttmpLienRenewals
SELECT DISTINCT V.ReferralID, V.PropertysSequenceNumber,
V.County, O.LOName, V.CaseCtyCode,
C.ConsultantsName, V.CaseName,
V.CaseID, V.LienFileNumber,
V.LienFileDate, V.PropertysCounty,
V.PropertysState, V.LienPrintDate
INTO ttmpLienRenewals
FROM tblFieldConsultants C LEFT OUTER JOIN tblLocalOffices O ON
C.ConsultantsID = O.FieldConsultantsID RIGHT OUTER JOIN
vuePullLienRenewals V ON O.LocalOfficeID = V.County
ORDER BY V.County, V.CaseName;
Or you can choose to have the report to send a command to drop the table
when the report closes if you don't want it to stick around between runs.
Ron Kunce
