February 14, 2006 at 1:44 pm
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
February 14, 2006 at 2:05 pm
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
February 14, 2006 at 2:35 pm
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
February 14, 2006 at 2:51 pm
If you need temp tables, move the code to a stored procedure on the SQL server and call it from the .ASP code.
February 15, 2006 at 3:05 am
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.
February 15, 2006 at 6:41 am
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.
February 16, 2006 at 8:58 pm
If possible you can use table varibale and this problem will not arise.
regards
February 16, 2006 at 10:07 pm
Not quite true... table variables are also connection sensitive...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply