Possible to Select * Into #temp From tblx and read #temp with ado.

[Guest]

Hello,

I have a complex query on sqlsrv2000 that runs better if I use an
intermediate #temp table. I have tried using a dataAdapter, Command Object
to

da.SelectCommand.CommandText = "Select * Into #temp From tblx"
da.SelectCommand.ExecuteNonQuery

But I get an error. The goal is to then use that same #temp table in a
subsquent query. I am thinking that since I am still on the same connection
- the #temp table would persist. But that does not seem to be the case.
Any suggestions appreciated if this is doable and how - or if I am limited to
using a stored Proc are table on the disk for something like this.

Thanks,
Rich

 

[William \(Bill\) Vaughn]

There are several issues with ADO.NET 2.0 (and earlier) that make using
#temp tables tougher than it has to be. The problem is that the #temp tables
belong to a specific connection and once you close the connection they are
released. This makes them virtually impossible to use from ASP apps and
while possible to use from Windows Forms, it requires a persistent
connection. On top of this issue, ADO.NET does not behave itself when
executing some queries involving #temp tables.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
_________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)


INETA Speaker
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.
__________________________________

 

[Thomas Weingartner]

Rich

In a current project we used a pattern of temporary table names like "temp" + GUID. This table will survive closing/opening connection.

In case of an unexpected program termination, we will cleaning up any temp* tables during startup.

Greetings from Switzerland
Thomas