?
Solved

database session duration

Posted on 2005-04-28
9
Medium Priority
?
240 Views
Last Modified: 2010-03-19

It is said that temporary tables created in a script exist for the duration of the session.  Lets say you type some commands in the query analyzer, and run the query.

The session must start when you press the run button, and the session must end when the last command of the script has been implemented, no?

Because when I run the following:
Use Contacts
Select * Into MyNewTempTable from dbo.Persons

I do not see any temporary table created in tempdb...so I take it that it is created then deleted?

What exactly is a session?
0
Comment
Question by:dhilwala2001
  • 6
  • 3
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890082
You are not creating a temporary table.  A temporary table would look like this:
Select * Into #MyNewTempTable from dbo.Persons


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890129
>>The session must start when you press the run button, and the session must end when the last command of the script has been implemented, no?<<
Nope.  A seesion starts when you open the connection and end when you close it.

>>I do not see any temporary table created in tempdb...so I take it that it is created then deleted?<<
Also temporary tables show up in tempdb as table name plus a system generated suffix.

Normally you should not use system tables, but this should illustrate what is happening:
select * into #MyTemp from categories
select * from tempdb..sysobjects where name like '#MyTemp%'
drop table #MyTemp
0
 

Author Comment

by:dhilwala2001
ID: 13890662
sorry, I meant #MyNewTempTable, like you said...
i did not see the temp table...
i will try again in a few minutes...in the middle of creating a new question...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890684
>>i did not see the temp table...<<
As I mentioned, it will not have the exact same name. I get this for the name:
#MyTemp_____________________________________________________________________________________________________________000000008871


0
 

Author Comment

by:dhilwala2001
ID: 13890724

I looked at ALL the tables that were created, but I saw no date time stamp that reflected today.

I am looking at the tempdb database, under tables.
There is NO table created TODAY....

All I did was
Select * Into #SHUMBHOO  from Persons

I ran the query analyzer, but I DID NOT CLOSE the query analyzer window.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890799
Just run the script I posted and see for yourself.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13890806
In your case it would be:

Select * Into #SHUMBHOO  from Persons
select * from tempdb..sysobjects where name like '#SHUMBHOO%'
drop table #SHUMBHOO
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 13890874
If that was not clear enough, let me just add this:  You cannot see tables created with a different connection. So you can spend all day looking at TempDB with Enterprise Manager refreshing the tables object and you will never see any new tables.
0
 

Author Comment

by:dhilwala2001
ID: 13891196
i see, thanks!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question