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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Seeing Temporary tables
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mb
Starting Member

United Kingdom
16 Posts

Posted - 05/07/2002 :  11:18:37  Show Profile  Reply with Quote
Does anyone know how can i see whether a temporary table exists? If I query sysobjects I cant see the table.

macka
Posting Yak Master

United Kingdom
162 Posts

Posted - 05/07/2002 :  11:24:36  Show Profile  Reply with Quote
If you create a temporary table with query analyser then you will be able to see it in the sysobjects table in the tempdb database. BUT if its created in a stored procedure then it will automatically be dropped when the stored proc completes.



Go to Top of Page

mb
Starting Member

United Kingdom
16 Posts

Posted - 05/07/2002 :  11:38:28  Show Profile  Reply with Quote
Thanks Macka, Is there anyway I can tell if a temporary table has been created for a particular session?

Maybe its better if I explain what I'm trying to do. Im trying to insert an application username into a temporary table and have triggers pick up the value and insert it into an audit table. However priviledged users may also make databases changes but will not use a temporary table. So I need to get the trigger to first check to see whether the temp table exists and if it does get the app username from it, if it doesnt then just use system_user.

Go to Top of Page

macka
Posting Yak Master

United Kingdom
162 Posts

Posted - 05/07/2002 :  11:51:31  Show Profile  Reply with Quote
First thing to say is that if you create a temp table called #temp, it won't actually be called that in the sysobjects table. Quote from BOL:

"If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters."

Secondly, by the time your application has finished working with the temp table, the table won't exist for the trigger to pick up. You might want to take a look at global temporary tables - but I think you may face a similar problem.



Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 05/07/2002 :  13:00:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
If you are using an application that interfaces with your SQL Server database, you can modify its connection information to pass a Workstation ID that you can access in SQL Server using the HOST_NAME() function. That lets you avoid the temp table completely, which I honestly don't think will work properly no matter what you do.

Look in Books Online under HOST_NAME() and HOST_ID() for more information. I know that ODBC under MS Access will automatically include a workstation ID with SQL Server connections, you can test this and get the proper format to use when connecting to the SQL Server.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000