May 13, 2002 at 3:51 pm
I have a stored proc that creates a dynamically named table. The table name is based off the datetime to milliseconds. It then populates this table with data and manipulates this data, to later drop this table. However, the drop is at the end of the proc call. So, in query analyzer, if I run it, the table that is created is dropped. However, if I start the execution of the proc before its finished, then the table hangs around.
How scan i ensure that this table is dropped at the end of the proc call, if it never reaches that section of code?
May 13, 2002 at 4:29 pm
Do you mean if when you stop the execution of the Procedure the tables remains? If so there is nothing you can call to directly drop the table unless you create another table for cleanup and set a pprocedure to check for the existance of tables with the names stored in your controller table and make sure they a dropped. So at the begining of your SP you would insert the record into the controller table to make sure you have a reference to check for, also maybe use a datetime field in the controller table to makes sure you do not kill currently in use tables. Or instead of using a permanent table use a temp table so that whne you disconnect from the connection it was created in, it will go away, of course this means you could potentially have serveral tables at a time if you hold the connection. See BOL about temp tables defined with # for more details.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 13, 2002 at 6:28 pm
I agree with Antares - is there any reason for not using a real temp table? -You would eliminate 'dropping table' problem right away.
May 14, 2002 at 9:26 am
Well, I have a series of stored procedures that perform various functions, all needing access to this temporary real table. Initially I wanted to use true temp tables, but the way that I designed my procs, modular, so that they can be reused, i simply pass the name of the table into them, and they can use dynamic sql to access this real table, then it is dropped at the end.
I have been unable to create a temp table that is visible beyond the scope of that proc that created it. So I moved to real tables. I have thought of referencing the table in tempdb..tablename, but that is a real table as well, and I beleive is not dropped when alls closed out anyway, so i'd just be moving the temp table to another db, tempdb.
I'd love to give you a run down of the entire process. Its basically a modular set of stored procs. Functionality that is used from many different places has been modularized. Bad part is now the table has to be real, and name is passed around for processing.
I have been thinking of logging it, and having a process remove them after a time. However, thats just a bandaid on a bad design, maybe. Anyway, still thinking of things to do.
I appreciate any help you folks offer.
thanks.
quote:
I agree with Antares - is there any reason for not using a real temp table? -You would eliminate 'dropping table' problem right away.
May 14, 2002 at 9:34 am
Have you used ##temptable or #temptable? - it would make a difference for the scope
May 14, 2002 at 11:37 am
well, i'll be a monkeys uncle. I tested with temp tables (#temp) and found that they did not work within multiple procs, and with dynamic sql. (The core of my code uses dynamic sql) So, I retested with ##temp tables, and its just fine that way. Man, that solves so many problems. Now I just need to rewrite everything to use that... and I'm sittin pretty. Thanks so much.
quote:
Have you used ##temptable or #temptable? - it would make a difference for the scope
May 14, 2002 at 12:01 pm
Can you briefly explain the difference between ##temp and #temp?
quote:
Have you used ##temptable or #temptable? - it would make a difference for the scope
May 14, 2002 at 1:18 pm
From BOL:
Temporary Tables
You can also create temporary tables. Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.
The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from computers running Microsoft® SQL Server™. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.
For example, if you create a table named employees, it can be used by any person who has the security permissions in the database to use it, until it is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect.
May 14, 2002 at 1:20 pm
one # sign is local temp table for procedure.
two # pound signs is global for connection
three # signs is global for all connections
i think only SQL 2000 supports the last one.
quote:
Can you briefly explain the difference between ##temp and #temp?quote:
Have you used ##temptable or #temptable? - it would make a difference for the scope
May 14, 2002 at 2:35 pm
Sorry there is no ### def for temp tables. From SQL 2000 BOL (updated)
quote:
There are two types of temporary tables:Local temporary tables
The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.
Global temporary tables
The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 05/14/2002 2:35:29 PM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply