Temporary table disappears before connection is closed

[Lucvdv]

I'm having a little problem using a temporary table (MSDE as database
engine).

Dim cn As New SqlConnection("Data Source=" & SQLServerName _
& ";Integrated Security=SSPI;Initial Catalog=TestDB;Pooling=No")
Dim cmd As New SqlCommand("", cn)
Dim tx As SqlTransaction

cn.Open()
tx = cn.BeginTransaction
cmd.Transaction = tx

cmd.CommandText = "SELECT * INTO #TEMP FROM [SomeTable] WHERE (...)"
cmd.ExecuteNonQuery()

cmd.CommandText = "SELECT COUNT(*) FROM #TEMP"
nEntries = cmd.ExecuteScalar

This fails with "Invalid object name '#TEMP'" in the 2nd query.
It succeeds when I replace all occurances of #TEMP by ##TEMP.
I tried the same in VB6 using ADO 2.8, and there it works with a single #.

#TEMP is used in a single connection, even a single transaction.
Shouldn't it exist as long as the connection exists?

Adding or omitting the "Pooling=No" parameter in the connection string
doesn't make any difference.



If I make it a global temporary table (##), how long will it exist?
Besides, it would require me to generate a unique table name to avoid
collissions if the application is running on two machines at the same time.


I'm using a temporary table for two reasons, of which I think the second
leaves no alternatives:
- several subsequent queries have to be executed that would otherwise all
contain a rather complex WHERE clause (selecting about half of the
records of the original table depending on their PK occurring in two
other tables, with secondary WHERE clauses on each of those).
I expect better performance by first copying the relevant records to a
temporary table.
- I want to create a snapshot of those records, to mask off alterations in
the real table while this is running.

 

[Rogas69]

why don't you use stored procedure? it is much better and faster to do what
you need.
and you won't have such problems with temp table scope, and your code will
be much simpler

Peter

 

[William \(Bill\) Vaughn]

This is a workable strategy and I have done it many times before. You can
also add an index to the #temp table to help performance if that makes
sense.
First, remember that T-SQL supports multiple statements in a single
CommandText. This means you can execute the SELECT * INTO ... and the
SELECT Count(*) in a single commandtext. However, if you capture the
rowsaffected from the SELECT * INTO you should get the number of rows added
to #temp. Note that if you create a #temp in a SP, it is dropped when the SP
ends so SPs are not always a good choice here. I'm not sure why you're
binding this in a transaction though...

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Lucvdv]

I'm not sure why you're
binding this in a transaction though...

Originally I started the transaction (which is there for something else)
only after creating the temporary table. I expanded it around the
temporary table to see if it would help keep it accessible.

I could (and probably should) move it back.


I'm not using a stored procedure because the results of actions outside of
the database, inbetween queries, may make a rollback necessary.

 

[gabe garza]

Try this.
Dim cn As New SqlConnection("Data Source= sqlsrv;Integrated
Security=SSPI;Initial Catalog=northwind")
Dim cmd As New SqlCommand("", cn)
Dim cmd1 As New SqlCommand("", cn)
Dim nEntries As Integer

cn.Open()

cmd.CommandType = CommandType.Text
cmd.CommandText = "select * into #temp from customers where ContactTitle
= 'owner'"
cmd.ExecuteNonQuery()

cmd1.CommandType = CommandType.Text
cmd1.CommandText = "SELECT COUNT(*) FROM #temp"
Try
nEntries = cmd1.ExecuteScalar
Catch ex As Exception
MsgBox(ex.Message)

End Try

cmd.CommandType = CommandType.Text
cmd.CommandText = "drop table #temp"
cmd.ExecuteNonQuery()

But on my system I can still change cmd1 to cmd and I still get 17 for
nEntries. I'm using VB.NET in Visual Studio 2003.