Am having trouble reading from a temporay table.
-
I am having trouble trying to read from a temporary table.
I am using temporary tables for the first time and I've hit a brick wall. The problem code is in the third of 3 sections.
Section 1 - Establishes if the temporary table already exists. If it does then it will get deleted.
Section 2 - Creates the temporary table and inserts data into it.
Section 3 - Tries to read from this temporary table, but it results with the following error.
Invalid object name '#tempReview'.
The first 2 steps run without error (because I've previously placed a response.end statement there and am able to see the response.write information). The problem comes about when I try to read from the temporary table. Can anyone please advise on what I'm doing wrong??
Here's the code.......
'Deleting the temporary table if it already exists within this session.
strSQL = "IF EXISTS (SELECT * FROM sysobjects WHERE NAME='#tempReview' and XTYPE='U') "
strSQL = strSQL & "DROP TABLE #tempReview"
Set rs = Conn.Execute(strSQL)
response.write "strSQL = " & strSQL & "<br>"
'Creating a temp table and inserting ISBN & ID into it, from NewReviews table
strSQL = "SELECT ISBN, ID "
strSQL = strSQL & "INTO #tempReview "
strSQL = strSQL & "FROM NewReviews "
Set rs = Conn.Execute(strSQL)
response.write "strSQL = " & strSQL & "<br>"
'Now the temp table has been created and has data within it - am now trying to read this data FROM it
strSQL = "SELECT * FROM #tempReview"
Set rs = Conn.Execute(strSQL)
rs.Open strSQL, conn, adOpenStatic
response.write "strSQL = " & strSQL & "<br>"
response.write rs("ISBN")
Thank you
-
In your 3 sections you have 3 seperate con.execute which i'm guessing is creating a seperate connection to SQL Server each time. When declaring a #temp table i believe it is only associated with the connection that created it and no other connection can see it, you should be able to create a global temporary table by declaring it with two ## i.e. create table ##temp, the table will be automatically deleted when any connections referencing it are disconnected.
HTH
Growing old is mandatory, growing up is optional -
Thanks Bond007, I've replaced the Set rs = Conn.Execute(strSQL) lines with rs.Open strSQL, conn, adOpenStatic and it appears to working fine. Now for a more thorough test.
Much appreciated, I've been trying to resolve this for over a day now. Ahhh, the relief.
Here's the revised code in case it helps anyone else.
'Deleting the temporary table if it already exists within this session.
strSQL = "IF EXISTS (SELECT * FROM sysobjects WHERE NAME='#tempReview' and XTYPE='U') "
strSQL = strSQL & "DROP TABLE #tempReview"
rs.Open strSQL, conn, adOpenStatic
response.write "strSQL = " & strSQL & "<br>"
'Creating a temp table and inserting ISBN & ID into it, from NewReviews table
strSQL = "SELECT ISBN, ID "
strSQL = strSQL & "INTO #tempReview "
strSQL = strSQL & "FROM NewReviews "
rs.Open strSQL, conn, adOpenStatic
response.write "strSQL = " & strSQL & "<br>"
'Now the temp table has been created and has data within it - am now trying to read this data FROM it
strSQL = "SELECT * FROM #tempReview"
rs.Open strSQL, conn, adOpenStatic
response.write "strSQL = " & strSQL & "<br>"
response.write rs("ISBN") & ", " & rs("ID")
response.End()
-
Glad to have helped, I've learnt so much from this site its always nice to be able put something back in to it.
Growing old is mandatory, growing up is optional -
Grrrrrrr - it's me again. After I entered the above alterations, I repeatedly pressed the refresh button and repeatedly got the results that I wanted. Brilliant! I then thanked Bond007 on this forum, returned to my (other) browser, pressed refresh and got presented with the original error again!
This is driving me round the twist.
It's looks like there's a link with what Bond007 said about the CONN command, but I'm at a loss as to what it is.
Please help a 'fast-balding' developer.
-
As long as you only open the connection once i would have thought it would be ok.
Also, i don't think you need the IF EXISTS bit at the top. Temp tables reside in tempdb and you can have mutiple temp tables with the same name coming from different connections, once a connection is closed the corresponding temp table is deleted. Try opening 2 query analyzer windows and execute
Create Table #tmp (id int)
In both windows, then run from one of the windows:
Select * from tempdb.dbo.sysobjects where name like '#%'
and you should see two tables. Close one of the windows and run the query again, you should see what i mean.
Could you place all this in a Stored Proc and then return the results from there, resulting in just one execute statement? Or just query NewReviews directly?
Growing old is mandatory, growing up is optional -
Hi,
Just try to do all this in one single batch. All theses statements can be put together in one single execute like this.
strSQL = "IF EXISTS (SELECT * FROM sysobjects WHERE NAME='#tempReview' and XTYPE='U') "
strSQL = strSQL & "DROP TABLE #tempReview"
strSQL = strSQL & "SELECT ISBN, ID "
strSQL = strSQL & "INTO #tempReview "
strSQL = strSQL & "FROM NewReviews "
strSQL = strSQL & "SELECT * FROM #tempReview"
Set rs = Conn.Execute(strSQL)
rs.Open strSQL, conn, adOpenStatic
response.write "strSQL = " & strSQL & "<br>"
response.write rs("ISBN")
You can add the "set nocount on" sql command at the beginning if this query is run very frequently, it will improve the performance a bit.
-
Thanks Maurice,
I'm not at my own PC until Tuesday (2nd August) and will definitely give this a try.
It looks similar to my attempt but with a few subtle, and hopefully, all-important changes.
Roll on Tuesday and thanks for the example. It's a cut and paste answer - perfect
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply