Disappearing SQL Server Temp Tables when ActiveX DLL 
Author Message
 Disappearing SQL Server Temp Tables when ActiveX DLL

I have been having a problem with using some temp tables on SQL Server
(v7 and 2000) that really is driving me batty!!!!!

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
an ActiveX 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

THANKS!!!!!



Tue, 24 Aug 2004 06:01:10 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Disappearing SQL Server Temp Tables when ActiveX DLL

2. SQL Server & Temp tables

3. temp tables - ADO - MS SQL Server

4. SQL Server Temp Tables

5. SQL Server Temp Tables in VB 6.0

6. Mysteriously Vanishing SQL Server Temp Tables...

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. VB ActiveX DLL ADO and SQL Server problem

12. SQL 7 Views and Temp Tables Performance

 

 
Powered by phpBB® Forum Software