Temp Tables question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Temp Tables question

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2005-11-02 : 21:45:29

Hi there,

I need to create a Temp Table for a search query I am constructing and am a bit worried that I will get errors returned from SQL cause the temp table already exists. The application is Active Server Pages (.asp) and SQL 2000. A basic rundown is this;
1. User enters some keywords into a text field and clicks submit
2. .asp page calls up a stored procedure p_SEARCH_tblProducts_frontend '" & REPLACE(sKeywords, "'", "''") & "'"

3. Stored proc takes the keywords and converts them into a set (http://www.sqlteam.com/item.asp?ItemID=5857)

4. Stored proc builds a result list that I want to save to a temp table (create #temptable) so I can further refine the results by several parameters

5. Once complete return the results and drop the temp table

My concerns are if another user tries a search inbetween steps 4 and 5 then they will receive an error stating the table already exists. What is the best way to create a temp table that is unique to that call to the stored procedure?

thanks -matt

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-11-02 : 23:05:00

Hi Matt,

This will never give error as these are temporary tables and not physical permanent table. These tables are created for a session i.e they are session specific. If in the mean time of your search some other users searches they will get another session.

This article by Graz explain the creation of temporary tables with session id beautifully.

http://www.sqlteam.com/item.asp?ItemID=2029

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2005-11-02 : 23:33:38

thanks for your reply.
My understanding is that they are stored for that users session. In .asp all connections are made via a connectionstring which specifies what user to connect under:

Provider=SQLOLEDB; Data Source=MyServer\SQL2000; Initial Catalog=myDatabase; User ID=myUser; Password=myPassword

In the above example, if one or more users connect to my .asp application they all connect under the myUser account. Any Temp tables created will be created under myUser and stored until the connection is closed.

From memory this happened to me once before and scared me off using temp tables for high traffic sites? I am probably wrong though...

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-11-03 : 02:39:39

Mat,

The user id which you are using in the connection string is same but the sessions are different for each user.

Thus there will never be problem.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2005-11-03 : 23:33:56

Hi Sachin,

ok, hopefully I will not have a problem then. Does connection pooling affect the sessions? i.e. several sessions using the one connection?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources