I get Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name '#t_testTemp484883646'.
when I run the following ASP code:
Code is below
<!#Include File="../includes/adovbs.inc" -->
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<%
'-- Setup database connection
Set DataCmd = Server.CreateObject("ADODB.Connection")
Set rsData = Server.CreateObject("ADODB.RecordSet")
Dim varTempTableName
DataCmd.Open Application("DB")
'-- Use a Temp Table so that it gets destroyed automatically in case the page times out
'-- Use a Global Temp Table so that it is available across scopes when I open the record set below
'-- Append a semi unique number to the end because its global it could conflict with another browser running the same report
'varTempTableName = "##t_TestTemp" & Minute(Now()) & Second(Now())
varTempTableName = "#t_TestTemp" & Session.SessionID
strSQL = "Create Table " & varTempTableName & "(TestColumn2 int)"
DataCmd.Execute strSQL
strSQL = "select * from t_Test"
rsData.Open strSQL, DataCmd, adOpenForwardOnly,adLockReadOnly
Do While Not rsData.EOF
varTestColumn2 = rsData("TestColumn2")
'Error occurs when I place the following insert statement. Code works with the global variable
strSQL = "Insert into " & varTempTableName & " (TestColumn2) values (" & varTestColumn2 & ")"
DataCmd.Execute strSQL
rsData.MoveNext
Loop
rsData.Close
strSQL = "Drop Table " & varTempTableName
DataCmd.Execute strSQL
Set rsData = Nothing
Set DataCmd = Nothing
%>
</BODY>
</HTML>
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
