Temp Tables / Sessions

[WHUXMAN]

I've inherited some Access apps that make connections to SQL Server 2005, create temp tables, extract data and write the results to a local Access database

AS I understand, each connecton made creates it's own environment on the server which isolates the temp tables from the other tables etc.

I'm pretty new to this funtionality and would appreciate any input on server sessions and what happens with the temp tables if the connecting app err's out or the connection terminates before the tables are dropped in code. Thanks in advance.

Wayne

 

[jrbarnett]

Here are some very basic information:

Temporary objects start with the character #
eg #MyTempTable.
Items that start with one # are visible only to the current connection (you can find out this for the current connection by running SELECT @@SPID).
This is how different users have their own connections and set of tables.

Such items are automatically dropped when the connection that creates it is closed, there is no need to specifically drop them.

Items that start with two # symbols - eg ##SharedTempTable
are visible to all connections open at the time the table is created.
It is deleted when the last connection open at the time is closed, or SQL Server is stopped or restarted.

Regardless of the current database, temporary objects are created in the tempdb database. The contents of this database is not persistent - ie it doesn't get saved between server restarts.

Hope that this is useful getting you started.

John