Temporary Tables

Does anybody know how long by default temporary tables last? How do you adjust temporary table lifetime? Is it possible to increase their lifespan?

the book next to me says that local and global temporary tables stored in the tempdb last for as long as the connection that created/calls them. They are then dropped by SQL Server unless you explicitly drop them.

If you want to keep a temp table for longer it would be better to stick it in a table variable IMO (or create a "temporary" table explicitly in the database.)

So I have no control over this if I am using a third-party app that utilizes an ODBC connector for its SQL queries and cannot edit the source code? I can't extend the lifetime of a spid as it sits idle? There's no master idle connection timeout that I can extend?

the only way I know to extend the connection timeout is in the source code for the app. But I think that will be different from the spid as you can start several processes running in one connection.

You can get ODBC statistics, and they might tell you how long the processes are really lasting?

The processes don't really last all that long. What happens is the user runs one batch that generates a temporary table and a "view" (not a sql view) of the data they are manipulating. They then let the application sit idle. They push paper around on their desk, go get a cup of coffee, whatever. When they return and continue to work with the data on screen, the temporary table has expired and they get a runtime error saying "select * from #temptable" is referencing an invalid database object.

I've been studying .NET lately, and this sounds like the perfect situation to use a dataset. It holds all the tables on the local client and only connects when prompted, like to make updates etc. I suppose that depends on the app you're using and whether the developers will sort that.

Either that, or you could output the data to an XML file and hold it locally on the client or on the network, depending on how often the data is updated.