Dropping a #Temptable... 1

[mmaz]

I'm creating a temp table from my ASP, using SQL Server and the following methodology:

myQuery = "SELECT * INTO #Temp1 FROM myTable WHERE bla bla..."

set myRs = server.createObject("ADODB.Recordset&quot
set myRs = conn.Execute(myQuery)

My question is, do I need to destroy this table and if so, what is the syntax??

Thanks!

 

[ixian]

Mmaz,
<%
...... your code

Set myRS = nothing

End Sub
%>
After all that is done

 

[mmaz]

Thanks, but I can't set the recordset to nothing because I'm calling a Crystal report from my ASP, and the report object must be able to access the recordset whenever the user selects the &quot;next page&quot; arrow.

I know that when the user closes the browser, the temp table will be dropped, but I read somewhere that this does not always work as it should, and the temp table could then occupy a lot of memory.

Any suggestions?

Thanks!

 

[vasah20]

At the end of your SQL Statement, add DROP TABLE #tempTable.
that will drop your temp table from SQL Server.

My question is, why are you inserting into a temp table using an AdHoc SQL query? Why don't you just do
myquery = &quot;SELECT * FROM myTable WHERE bla bla...&quot;

set rs = cn.execute(myquery)

this will give you exactly what you need, without having to insert it into a #temptable.

hth
leo

 

[culshaja]

If the drop doesn't work use a client side cursorif you can for a disconnected recordset. That way you can close the connection as soon as the results are returned.

I have the same question as leo, why are you using a temp table? Unless you have a very compeling reason to do this you should avoid it as it is very proecessor and memory intensive.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[mmaz]

Oh, I wish I could do just that, but the situation will not permit this. You see, the key I'm looking for could be in 2 different fields in the same table. So I have a Union query inserting records into a temp table, and then I'm selecting from this temp table and passing the recordset to Crystal Reports.

I have tried the DROP TABLE #temptable statement, but I got an error message: #temptable does not exist in system catalog.

 

[vasah20]

why not do something like

'modify the sql statement as needed
set rs = cn.execute &quot;select * from mytable a where a.col1 = &quot; &key & &quot; union select * from mytable b where b.col2 = &quot; & key

then use the rs.filter method to filter the recordset down to what you need. This way, you won't need to bother with even creating a #tempTable.

I know that #tempTable only exists for the duration of the connection, and are deleted when you disconnect. I don't know why you can't issue the DROP TABLE statement... maybe it has something to do with the fact that you are issuing batch commands... you might want to try asking this in the SQL forum.

check the tempdb database in SQL Server to see if in fact your #tables are getting erased upon disconnection... by design, they should be, but like I said I've never used #tables in batch processing.

if you need more info on the RS.Filter property, see

http://www.devguru.com/Technologies/ado/quickref/recordset.html

hth
leo