March 3, 2004 at 6:46 am
Hello everyone,
I'm struggling with a strange problem, hope to get a help on it.
I've a storage procedure that does some calculations based in a couple tables, at the end of the day it builds a temporary table with the results.
Problems:
1 - If I stop/start the database, some data aren't calculated for that procedure, I need to drop and create it to make it work.
2 - Sometimes (not always) if 2 users use same procedure at same time, same error appears, and again I need to drop and create the procedure.
This storage only make updates on the #temporary table.
Any idea???
Thanks in advance!
RM
March 3, 2004 at 8:27 am
SQL Server has a database it uses as a scratch pad. That database is called tempdb. Anytime a table is created with a Pound Sign (#) as the first, or first and second characters of the table name it knows that the table is to be created in tempdb.
Tempdb is a temporary database. If SQL Server is shut down the contents of the tempdb are lost. When SQL Server is started again a new tempdb is initialized and on you go.
If you shut down the computer (SQL Server is shut down) the #Temporary table is lost.
Change the code to save the table in a permanant database and control the scope or life of the table programatically.
You can always check to see if an old table exists prior to processing by using the following code
IF EXISTS (SELECT *
FROM mydb..sysobjects
WHERE [name] = 'mytable'
AND [type] = 'U')
DROP TABLE mydb..mytable
If you are in the database in which you want to delete the table you can use the OBJECT_ID and OBJECTPROPERTY functions instead of name and type.
Did this help or did I miss the mark?
March 3, 2004 at 9:04 am
I've used temporary #tables because my SP create it at begin, and get rid of it when session is over, this SP can be (or should be) runned by several users at same time, therefore I can't use permanent tables because that would lock some of them.
March 3, 2004 at 10:10 am
There is a way to use permanant tables in conjunction with Dynamic SQL and SUSER_SNAME().
Each process can create it's own Permanant Table named 'mytable_' + SUSER_SNAME using Dyanmic SQL.
Since you use the same prefix 'mytable' and can always get the user's name (SUSER_SNAME()) you can always use Dyanmic SQL to Select, Insert, Update and/or Delete data.
By creating a local temporary table #mytable with the same structure as mytable_myuser and then using Dynamic SQL to move desired rows into it you can select any data you need too.
It's a bit bulky but it works. Other may have other ideas but this would work.
I hope it helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply