COMMIT with temporary (##) table?
-
One of our programmers claimed that a stored proc he wrote is not creating a temporary table. The account used to run the proc has the permissions so I looked at his code. Here is an edited extract:
CREATE PROCEDURE...
....
CREATE TABLE ##temptable (...)
INSERT ##temptable
VALUES(...)
SET @Err = @@ERROR
IF @Err <> 0
BEGIN
ROLLBACK
RETURN
END
COMMIT
...
Does COMMIT\ ROLLBACK apply to a temporary table?
TIA,
Bill
P.S. If I execute the proc from QA, then close the QA session, open a new QA and type " SELECT* from ##temptable", I get "table not found" as temp tables are dropped at the end of the QA session. I presume this lack of permanace of a temp table also applies when the stored proc is run programatically.
-
Try using only one #
CREATE TABLE #temptable (...)
INSERT #temptable
VALUES(...)
SET @Err = @@ERROR
IF @Err <> 0
BEGIN
ROLLBACK
RETURN
END
COMMIT
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
I see the commit and rollback, but where is the BEGIN....?
/Kenneth
-
You are using global temporary tables (the ones with two ## signs). Global temp tables are dropped automatically when the session that created them ends AND no-one else is referencing that table. From BOL:
"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. "
Regarding COMMIT effecting global temp tables, yes it does.
If you do not really need the functionality of the global temp tables and if the data set is not that much, using table variables would be better.
If you do need to use these, you can troubleshoot the code (put more instrumentation in - log into a physical table to troubleshoot, use print statements, use the T-SQL debugger etc.) to see whether it is getting created properly and whether it is being populated.
-
Thanks to all. Question: is there a difference in "lifespan"\visibility between the two types of temp table (i.e. "#" vs. "##")? (One responder suggested I use "#" instead of "##". Just wondering.)
TIA,
Bill
-
# is visible only to the connection who created it and is dropped when the connection ends or when explicitly dropped.
## is visible by anyone on the server. It is dropped only when all connection reffering to it are destroyed or when explicitly dropped.
-
This can clear something up for me as well.
I do not remember the thread, but I was told that Global Varaibles do work like Global Temp Tables.
In other words, I was told that @@Variable is the same as @Variable with an additional @ on the front. Is this accurate?
Thanks.
I wasn't born stupid - I had to study.
-
Is there a t-sql command to display all temporary tables that currently exist in tempdb? Can this be done in EM?
TIA,
Bill
-
Select name from tempdb.dbo.SysObjects where name like '#%'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply