Global Temporary table drops after another recordset is opened

Question

I currently open and fill a global table with the name ##GlobalTableMain via a macro at the moment. The table gets created the following way:

Public Sub ExecuteSQLQuery(sQuery As String)
Dim cn As New ADODB.Connection

cn.Open strConnection
cn.Execute "SET NOCOUNT ON;" & sQuery
cn.Close
End Sub

The Query roughly looks like this:

CREATE TABLE ##GlobalTableMain (Columns here);
INSERT INTO ##GlobalTableMain (Columns) VALUES
BUNCH OF ROWS

All of this worked just fine until I tried to add another macro that became necessary due to another factor.

The query in question:

Sub AggregateSQLTempTable(sTempTable As String, sAggClm As String)
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, sQuery As String
Dim selectClms As String, groupClms As String

cn.Open strConnection
sQuery = "SET NOCOUNT ON; SELECT tempdb.sys.columns.name FROM tempdb.sys.columns WHERE tempdb.sys.columns.object_id = Object_Id('tempdb.." & sTempTable & "')"
rs.Open sQuery, cn


sQuery = vbNullString
selectClms = vbNullString
groupClms = vbNullString

If Not (rs.EOF Or rs.BOF) Then
    rs.MoveFirst
    Do While Not (rs.EOF Or rs.BOF)
        selectClms = selectClms & IIf(Len(selectClms) > 0, ", ", "") & IIf(rs!Name = sAggClm, "SUM(" & rs!Name & ") " & rs!Name, rs!Name)
        groupClms = groupClms & IIf(rs!Name = sAggClm, "", IIf(Len(groupClms) > 0, ", ", "") & rs!Name)
        rs.MoveNext
    Loop

    sQuery = vbNullString
    sQuery = "SELECT * INTO #aggTempTable FROM (SELECT " & selectClms & " FROM " & sTempTable & " GROUP BY " & groupClms & ") a;"
    sQuery = sQuery & Chr(10) & "TRUNCATE TABLE " & sTempTable & ";"
    sQuery = sQuery & Chr(10) & "INSERT INTO " & sTempTable & " SELECT * FROM #aggTempTable;"
    sQuery = sQuery & Chr(10) & "DROP TABLE #aggTempTable;"

    cn.Execute sQuery
End If

rs.Close
cn.Close
End Sub

Supposedly SET NOCOUNT ON should prevent this but it doesn't work for me unfortunately.

It is a scope issue. Temp table evaporate as soon as the session is closed. http://stackoverflow.com/questions/892351/sql-server-2005-and-temporary-table-scope — John Cappelletti, Oct 13 '16 at 19:35
That's why I'm using a global one. And it all works perfect if I go from the creation of the temp table to a pivot table that uses the temp table to run a query. — Spurious, Oct 13 '16 at 19:39
Global temporary tables are visible to any user and any connection after they are created, and are **deleted when all users that are referencing the table disconnect from the instance of SQL Server.** See [this](http://stackoverflow.com/questions/2920836/local-and-global-temporary-tables-in-sql-server). — John Wu, Oct 13 '16 at 19:41
But why does it work then if I go from creation to the next macro that fills the `CommandText` of the pivot table which then in turn references the global temp table. Do you have any solution to my problem? How can I share the data between the several procedures? — Spurious, Oct 13 '16 at 19:50

score 0 · Answer 1 · answered Oct 14 '16 at 10:24

I've found a solution that helps me do this.
I went ahead and made the Connection a public variable and added the following two macros. Here are all the new macros:

Public cn As New ADODB.Connection

Public Sub OpenSQLConnection()
If Not cn.State = adStateOpen Then cn.Open strConnection
End Sub

Public Sub CloseSQLConnection()
If Not cn.State = adStateClosed Then cn.Close
End Sub

Public Sub ExecuteSQLQuery(sQuery As String)
OpenSQLConnection
cn.Execute "SET NOCOUNT ON;" & sQuery
End Sub

Sub AggregateSQLTempTable(sTempTable As String, sAggClm As String)
Dim rs As New ADODB.Recordset, sQuery As String
Dim selectClms As String, groupClms As String

OpenSQLConnection
sQuery = "SET NOCOUNT ON; SELECT tempdb.sys.columns.name FROM tempdb.sys.columns WHERE tempdb.sys.columns.object_id = Object_Id('tempdb.." & sTempTable & "')"
rs.Open sQuery, cn

sQuery = vbNullString
selectClms = vbNullString
groupClms = vbNullString

If Not (rs.EOF Or rs.BOF) Then
    rs.MoveFirst
    Do While Not (rs.EOF Or rs.BOF)
        selectClms = selectClms & IIf(Len(selectClms) > 0, ", ", "") & IIf(rs!Name = sAggClm, "SUM(" & rs!Name & ") " & rs!Name, rs!Name)
        groupClms = groupClms & IIf(rs!Name = sAggClm, "", IIf(Len(groupClms) > 0, ", ", "") & rs!Name)
        rs.MoveNext
    Loop

    sQuery = vbNullString
    sQuery = "SELECT * INTO #aggTempTable FROM (SELECT " & selectClms & " FROM " & sTempTable & " GROUP BY " & groupClms & ") a;"
    sQuery = sQuery & Chr(10) & "TRUNCATE TABLE " & sTempTable & ";"
    sQuery = sQuery & Chr(10) & "INSERT INTO " & sTempTable & " SELECT * FROM #aggTempTable;"
    sQuery = sQuery & Chr(10) & "DROP TABLE #aggTempTable;"

    cn.Execute sQuery
End If

rs.Close
End Sub

Global Temporary table drops after another recordset is opened

1 Answers1