I have a very long stored procedure running with a temp table in it. While the stored procedure is running (and the temp table hasn't been dropped) How can I query the temp table.
I've tried something like the following with no success:
Select * from tempdb..#tempTableu can try these options
select * from #tempTable
select * from tempdb.#tempTable|||From BOL :
If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
* A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
* All other local temporary tables are dropped automatically at the end of the current session.
* 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.
---------
Hence try using ## (global temp tables), if you need to query outside the stored procedure.|||Can you give me an example in a select statement. The temporary table shows up in tempdb sysobjects as:
#Dup______________________________________________ __________________________________________________ ________________00000000001A|||example
use pubs
go
select * into ##temp from authors
select * from ##temp
--or
select * from tempdb.##temp
--or
select * from tempdb..##temp|||I get "invalid object name" trying any of these. I even tried it straight from the tempdb.|||Give your stored procedure which creates the temp table and explain how you have queried the same outside the procedure.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment