October 28, 2009 at 2:25 pm
I'm having issues with a stored procedure that uses tables. If more than one user/process executes the stored procedure at the same time, I get .net errors saying that the table cannot be found.
Here is how I create the tables:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[parkingMap]') AND type in (N'U'))
DROP TABLE [dbo].[parkingMap]
CREATE TABLE [dbo].[parkingMap](
[oldMapID] [uniqueidentifier] NOT NULL,
[newMapID] [uniqueidentifier] NOT NULL,
[oldSpaceID] VARCHAR(50) NOT NULL,
[newSpaceID] VARCHAR(50) NOT NULL
) ON [PRIMARY]
Once the table is created, I then use the table throughout my ASP.NET application.
Is this not the correct way to use temporary tables? Is there a better way?
Thanks!
October 28, 2009 at 2:29 pm
I believe you would need to have a pound sign (#) in front of your table name to create it as a temporary table.
So you would reference it like:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#parkingMap]') AND type in (N'U'))
DROP TABLE [dbo].[#parkingMap]
CREATE TABLE [dbo].[#parkingMap](
[oldMapID] [uniqueidentifier] NOT NULL,
[newMapID] [uniqueidentifier] NOT NULL,
[oldSpaceID] VARCHAR(50) NOT NULL,
[newSpaceID] VARCHAR(50) NOT NULL
) ON [PRIMARY]
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
October 28, 2009 at 2:31 pm
That's not going to work out too well. There are a lot of options for doing something like this, but this one isn't going to support concurrency at all. Temp tables get around the concurrency issue, but they're dropped as soon as the connection is dropped, which means they won't be available if you're opening/closing connections between pages.
One option is to make one static table(like the one you already have) and add an additional parameter SessionID that you use to tie the rows in the table to each different User. Instead of dropping the table, you insert/delete rows.
Another is to use data tables etc. on the web server.
I'd look at the Session idea first, my web guys hate using memory on the web servers when they don't have to.
October 28, 2009 at 2:32 pm
Thanks!
Would 2 or more users then be able to run this stored procedure without "bumping" into each other?
I think the way I have it setup now, is that I am creating a table in the SPROC, then if another user runs it, it will first check for the tables existence, and if it does exist, it will DROP the table...but this leaves the 1st user in limbo!
My concern is, if I use "#", will I still be able to use the table throughout my asp.net app? Or is the #tmpTable dropped after the SPROC is finished?
Thanks!
October 28, 2009 at 2:37 pm
Err oops, forgot you specified you were using a stored procedure. Yes, they're dropped implicitly when the SP ends.
October 28, 2009 at 3:04 pm
just my 2ct
IMO you are trying to persist some kind of state of your "client" session.
An option can be to add a session identifier to your row a key component.
Another option is you could do that in a "sessions" table, providing it a session identifier and an XML column which contains the session payload (if very small).
In you case, this workload xml could contain your map info.
Provide such kind of sessions table with time info, so you are able to cleanup "orphaned" or incomplete sessions after a while.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2009 at 5:09 am
I agree. Temporary tables are meant for temporary storage within a single connection's life on the server, not for the entire time an application is being used. You need a persistance mechanism for state maintenance. Take a look at the ASPState process available with .NET. It creates a table for maintaining state within your app. That's what you need.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 29, 2009 at 7:22 am
Thanks everyone!
I don't think I would be able to use a .NET session or state table, because I need it to be in SQL Server.
Reason being, is that I JOIN this temporary table(temporary in the sense that it exists only for the life of the session) with my permanent SQL tables in my database. So while it does only exist for the session, I also use it with other tables, not just by itself.
Thanks!
October 29, 2009 at 7:29 am
Magy (10/29/2009)
Thanks everyone!I don't think I would be able to use a .NET session or state table, because I need it to be in SQL Server.
Reason being, is that I JOIN this temporary table(temporary in the sense that it exists only for the life of the session) with my permanent SQL tables in my database. So while it does only exist for the session, I also use it with other tables, not just by itself.
Thanks!
ASPState is a maintenance routine that runs inside SQL Server. However, I understand.
What you need to do is create a single table in SQL Server and then have one of the keys for the table be a unique identifier of some kind (a globally unique identifier would work) that you then pass back to the client so that it can use that same identifier for all it's calls. Then you can filter the data within your state maintenance table for each user during their sessions.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 29, 2009 at 8:30 am
Oh ok, so basically I could get the sessionID in asp.net, and then pass that to the SPROC. And inside my "temp" table, I could add another column for sessionID.
Then all of my queries would end with...
WHERE sessionID = @sessionID
Thanks!
October 30, 2009 at 8:03 am
I'm wondering why the table itself needs to be temporary. Couldn't the table stay permanent and you simply delete stale rows occasionally using an Agent job?
If you are indeed storing session or viewstate data I do this in my app using SQL tables; the .Net SessionID as you suggest make a good PK.
So, I guess I'm not sure why it's important to drop the table from db after every use.
October 30, 2009 at 8:45 am
you know what? I think you are right...now there is no reason for this to be a temp table!
Thanks!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply