Mysteriously Vanishing SQL Server Temp Tables... 
Author Message
 Mysteriously Vanishing SQL Server Temp Tables...

I am using a series of temp tables (created in the usual
way using # as the first char of the table name) in a VB
class.  Class works fine when called from within a VB
app.  However, when using the class as part of a DLL - a
class method seems to quite suddenly loose access to the
temp tables.  They are still in the sysobjects table for
tempdb, and the database connection is still up and
working.  But statements that reference the temp tables
(any of them) return an error that the table names are no
longer valid objects.  I believe the problem is related
to the ODBC driver (big surprise) and I have tried using
the most current XP compatible driver as well as earlier
versions under 2000 and NT.  No difference.  I have also
tried ADO 2.6/2.7 - no difference.  SQL Server 7/2000 -
no difference.

The problem happens in the following code sequence.

    ' do we have all the temp tables we need?

    If Not CheckTempTables(oDBConn, sString) Then

        ' no need to continue

        sSQLStatus = sString

        Exit Function

    End If

    ' count the records in the table

    Set rstTempExpandControl = New ADODB.Recordset

    sSQL = " SELECT Count(*) From #TempExpandControl "

    Debug.Print sSQL

    Set rstTempExpandControl = oDBConn.Execute(sSQL)

    lRecordCount = rstTempExpandControl(0)

    rstTempExpandControl.Close

    'clear memory

    Set rstTempExpandControl = Nothing

    If Not CheckTempTables(oDBConn, sString) Then

        ' no need to continue

        sSQLStatus = sString

        Exit Function

    End If

    sSQL = " SELECT ProjName, DGPdDtaBegBookVal, " & _

           " DRSiRecordType, DRSiResultNumber, DRSiRef,
DRSsIncludeNoi, " & _

           " DGPsEarliestDate, DRSsAcctMergePM,
DRSmAmounts " & _

           " FROM #TempExpandControl e " & _

           " ORDER BY e.ProjName, e.DRSiRecordType, " & _

           " e.DRSiResultNumber, e.DRSiRef "

    Debug.Print sSQL

    '#########################################

    Set rs = oDBConn.Execute(sSQL)
/* this statement above executes, and then the next
statement we attempt to use a temp table with will fail */

' I have tried every combination of recordset options I
could come up with, to no avail.

    ' do we have all the temp tables we need?

    If Not CheckTempTables(oDBConn, sString) Then

        ' no need to continue

        sSQLStatus = sString

        Exit Function

    End If

Of course, when you look at this, all seems fine - you
might even think I was lying!  But it just isn't so.

Help!!!!

Chris



Tue, 24 Aug 2004 06:33:14 GMT  
 Mysteriously Vanishing SQL Server Temp Tables...
Chris ,
temp tables are only visible to the connection they were created.  so I
beleive it works fine when you run the dll in VB IDE, but does not work
when you built and throw the DLL into COM+, because COM+ manages the
connection pool.
try to use Global temp tables. ( ##TableName ).

--
hth
Hirantha
Please reply only to the newsgroups.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 24 Aug 2004 07:24:24 GMT  
 Mysteriously Vanishing SQL Server Temp Tables...
Thanks for the reply.  I'm afraid we cannot use global
tables as a permanent solution, althougth I will test
with them.  If you look at the code, you will see that
the connection is not closed or recreated at any time, so
the temp tables associated with this connection should
still exist.

Quote:
>-----Original Message-----
>Chris ,
>temp tables are only visible to the connection they were
created.  so I
>beleive it works fine when you run the dll in VB IDE,
but does not work
>when you built and throw the DLL into COM+, because COM+
manages the
>connection pool.
>try to use Global temp tables. ( ##TableName ).

>--
>hth
>Hirantha
>Please reply only to the newsgroups.

>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.



Tue, 24 Aug 2004 09:08:59 GMT  
 Mysteriously Vanishing SQL Server Temp Tables...
Chris ,
another option is to try moving the logic to a Stored procedure, that
will gurentee the access to temp table.

--
hth
Hirantha
Please reply only to the newsgroups.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 24 Aug 2004 12:00:13 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Disappearing SQL Server Temp Tables when ActiveX DLL

2. SQL Server & Temp tables

3. Disappearing SQL Server Temp Tables when ActiveX DLL

4. temp tables - ADO - MS SQL Server

5. SQL Server Temp Tables

6. SQL Server Temp Tables in VB 6.0

7. Temp table in SQL server

8. temp tables in SQL Server

9. How can I copy an SQL selection to an empty temp table

10. SQL Temp Table

11. SQL 7 Views and Temp Tables Performance

12. SQL temp table result

 

 
Powered by phpBB® Forum Software