Stored procedure deleting temporary table

Question

I have a stored procedure which makes used of a temporary table with ##temp creating on the fly using select * into ##temp from tablename.

The problem I have having is this stored procedure seems to delete or make this available only for that moment in time when the query is ran, despite having ## which is global and can be used by other users from what i know.

I am using SSRS to pull the stored procedure and using drill through from this report to the same report, first one only showing charts, the second report which is the same stored procedure which uses the actions link via parameter but the second report doesn't recognize the ##temp table.

Now that you got the background, is there a way around this or a better way of doing it, keep in mind we don't have a data warehouse at the moment, so just using temporary tables to do the work around.

Thanks

If you want to persist the table, use a real table not a temporary one. Also, your SP will only run once and will fail on subsequent runs due to the table already existing. Use INSERT INTO ... SELECT ... rather than SELECT ... INTO ... — Chris Pickford
Agree with @ChrisPickford. Here are the docs on temp tables. There is a section (about halfway down, headed Temporary Tables) that details when you can expect a temp table to leave scope, and become unavailable. — David Rushton
Thanks Chris, my boss mentioned something about the SQL server belonging to a client so not to create a table, but I suppose a view will work just as good, right? without the same implications — abs786123
A view is just a query on existing tables so will be no use in this case. If you need to persist data, then you need to create a table. If you have some other use case then you'll need a different approach to your problem. — Chris Pickford
mmm...we need that dataware house in place maybe the best way forward — abs786123

maialithar maialithar · Accepted Answer · 2016-07-01T15:12:50

From MSDN:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

If you have admin access to the server, try this answer.

Stored procedure deleting temporary table

1 Answers