temporary tables

Question

temporary tables

Magy

SSCarpal Tunnel

Points: 4217
More actions
February 14, 2006 at 1:44 pm

#171137

I have a little asp script that keeps getting this error:
SQL Server]Invalid object name '#tablenettemp'.
My asp logic is:
DropTable(tablenettemp) 'function to check if temp table exist, drop if it does
strSQL = "select col1 AS JobNum, col2, col3 into #tablenettemp"    'select data into temp table
strSQL = strSQL & "FROM tbl1"
Set tempRs = Server.CreateObject("ADODB.RecordSet")      'connection string for temp table
TempRs.Open strSQL, objCn
strSQL = "select col1, col2, col3 FROM tbl2"
strSQL = strSQL & "LEFT JOIN #tablenettemp AS cnt ON tbl2.Rid=JobNum"
Set objRs = Server.CreateObject("ADODB.RecordSet")      'connection string for main query
objRs.Open strSQL, objCn

Now if I run this query in SQL Query Analyzer, it works. But when I run it from the webpage, I get the error above.
Can anyone see a flaw in my logic?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

What is the purpose of the 1st recordset (TempRS), if the SQL that is executed does not return a recordset ?

Why do you need a #temp table when this can easily be expressed as 1 chunk of SQL, with a derived table in place of the temp table ?

strSQL =

"SELECT col1, col2, col3

FROM tbl2

LEFT JOIN

(

select col1 AS JobNum, col2, col3 FROM tbl1

) cnt

AS cnt ON tbl2.Rid=JobNum "

Set objRs = Server.CreateObject("ADODB.RecordSet") 'connection string for main query

objRs.Open strSQL, objCn

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Everytime you run objRs.Open strSQL, objCn you open new connection.

#table exists only within connection scope. As soon as you close connection (objRs.Open strSQL, objCn finished) #Table is dropped automatically. Next connection you open has nothing to do with # tables from another connection.

_____________
Code for TallyGenerator

JeffB Hall of Fame Points: 3541 More actions · Answer 3

If you need temp tables, move the code to a stored procedure on the SQL server and call it from the .ASP code.

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 4

if you want to create a temporary table,

then you have to use the following Query:--

select col1,col2,col3 into #temptable

from table

other wise you have to create a temptable first.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Th question I have thou is why create the temp table at all? Based on the snippet of logic you should just simply left join tbl1 to tbl 2 instead and get the desired results.

Akku (shailendra Saxena) SSC Enthusiast Points: 117 More actions · Answer 6

If possible you can use table varibale and this problem will not arise.

regards

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

Not quite true... table variables are also connection sensitive...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)