Export/View data from a SQL Server temporary table

Question

I have a temp table, that isn't going away. I want to see what is in the table to determine what perhaps bad data might be in there. How can I view the data in the temp table?

I can see it in tempdb. I ran

SELECT * FROM tempdb.dbo.sysobjects WHERE Name LIKE '#Return_Records%'

To get the name of the table.

I can see it's columns and its object id in

select c.*
from tempdb.sys.columns c
inner join tempdb.sys.tables t ON c.object_id = t.object_id
where t.name like '#Return_Records%'

How can i get at the data?

By the way, this doesn't work

SELECT * FROM #Return_Records

Answer 1

This is something that seems like you obviously tried, but since you didn't mention it I though I would mention just in case:

Did you try "SELECT * FROM #Return_Records"?

Answer 2

How about select * from ##Return_Records?

Answer 3

SQL Server limits access to Local Temp Tables (#TableName) to the connection that created the table. Global temp tables (##TableName) can be accessible by other connections as long as the connection that created it is still connected.

Even though you can see the table in the table catalog, it is not accessible when trying to do a SELECT. It gives you an "Invalid Object Name" error.

There's no documented way of accessing the data in Local Temp Tables created by other connections. I think you may be out of luck in this case.

Answer 4

Like José Basilio says, that's a temporary table belonging to another connection. If it's there for a long time, it must belong to a connection that has been open for a long time. Check Maintenance -> Acitivity Monitor; you can sort by Login Time.

Check if the Login Time, or Last Batch Time, matches with the create date of the temporary table. That can be retrieved with:

select crdate from tempdb.dbo.sysobjects WHERE Name LIKE '#Return_Records%'

You can shoot down suspect connections (right click and Kill Process.) If the table is gone after killing a process, you've found the culprit.

To just remove the table, restart the Sql Server service. You can attach Sql Profiler right after with a filter to start looking for the connection that creates the temporary table.

Answer 5

One way of getting at the data in a low-level and not particularly easy to manipulate manner is to use the DBCC PAGE command as described in a blog post by Paul Randal:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx

You should be able to find the fileid and page number of the first page in the object by querying on sysindexes .. the last time I did this was on SQL Server 7.

If the data is in the database, then DBCC page will be able to dump it.

pjjH