0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
abs786123
  • 581
  • 2
  • 11
  • 24
  • 2
    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 Jul 01 '16 at 10:59
  • 1
    Agree with @ChrisPickford. Here are the docs on [temp tables](https://msdn.microsoft.com/en-us/library/ms174979.aspx). 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 Jul 01 '16 at 11:08
  • 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 Jul 01 '16 at 11:09
  • 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 Jul 01 '16 at 11:20
  • mmm...we need that dataware house in place maybe the best way forward – abs786123 Jul 01 '16 at 11:33

1 Answers1

1

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.

Community
  • 1
  • 1
maialithar
  • 3,065
  • 5
  • 27
  • 44