Sql tmp table and reports

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi

How can I use a temp table created in SQL Server 2000 as the basis for a
report in Access?

Thanks
 
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
 
ADDENDUM: You have to be the DBO or a member of the sysadmin group to Drop
the table. Therefore, if a non-sysadmin user is running the procedure, you
can change the DROP TABLE to a TRUNCATE TABLE to remove all rows before
recreating it.
 
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.
Click to expand...

I thought it was "the session connection ends" (for # tables) or "the server
is restarted" (for ## tables).
 
Back
Top