Cannot access a temporary table?

Question

I am using ADO/Visual C++ to access SQL Server database. I find it is OK to create only one temp table in the database. But if I create two temp tables and open recordset of one table, and access the other table, then I will get the following error:

Invalid object name ‘#TempTable1’

Below is my code:

    #include "stdafx.h"
    #include "TestTempTable.h"

    #ifdef _DEBUG
    #define new DEBUG_NEW
    #endif

    #import "msado15.dll" no_namespace rename("EOF", "EndOfFile")

    // The one and only application object

    CWinApp theApp;

    using namespace std;

    int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
    {
        CoInitialize(NULL);
        try {
            _ConnectionPtr cn("ADODB.Connection");
            _RecordsetPtr rs("ADODB.Recordset");
            CString strSQLQuery;
            ULONGLONG uIndex, uCount;
            _variant_t vtFirstName;

            cn->Provider = "sqloledb";
            cn->Open("Data Source='(local)';Integrated Security=SSPI;", "", "", adConnectUnspecified);

            //  Create a test database
            strSQLQuery = _T("CREATE DATABASE MyTestDB6;");
            cn->Execute(_bstr_t(strSQLQuery), NULL, 0);

            //  Use the test database
            strSQLQuery = _T("USE MyTestDB6;");
            cn->Execute(_bstr_t(strSQLQuery), NULL, 0);

            //  Create a temp test table
            strSQLQuery = _T("CREATE TABLE #TempTable1(Field1 bigint, Field2 int, Field3 smallint, Field4 tinyint, Field5 bigint, Field6 int, Field7 smallint, Field8 tinyint, Field9 float, Field10 datetime, Field11 nvarchar(20), Field12 nvarchar(40));");

            if (cn->Execute(_bstr_t(strSQLQuery), NULL, 0))
            {
                //  Initialize the total test count to 5
                uCount = 5;

                //  Add multiple records by invoking Execute for multiple times
                strSQLQuery = _T("INSERT INTO #TempTable1 VALUES(10000, 1000, 100, 10, 20000, 2000, 200, 20, 99.98, 1920/05/20, 'Hello', 'Hello, World!');");

                for (uIndex = 0; uIndex < uCount; uIndex ++)
                    cn->Execute(_bstr_t(strSQLQuery), NULL, 0);

                //  Create temp test table 2
                strSQLQuery = _T("CREATE TABLE #TempTable2(Field1 bigint, Field2 int, Field3 smallint, Field4 tinyint, Field5 bigint, Field6 int, Field7 smallint, Field8 tinyint, Field9 float, Field10 datetime, Field11 nvarchar(20), Field12 nvarchar(40));");

                if (cn->Execute(_bstr_t(strSQLQuery), NULL, 0))
                {
                    //  Initialize the total test count to 5
                    uCount = 5;

                    //  Add multiple records by invoking Execute for multiple times
                    strSQLQuery = _T("INSERT INTO #TempTable2 VALUES(10000, 1000, 100, 10, 20000, 2000, 200, 20, 99.98, 1920/05/20, 'Hello', 'Hello, World!');");

                    for (uIndex = 0; uIndex < uCount; uIndex ++)
                        cn->Execute(_bstr_t(strSQLQuery), NULL, 0);

                    //  Select from temp test table2
                    strSQLQuery = _T("SELECT * FROM #TempTable2");

                    if (SUCCEEDED(rs->Open(_bstr_t(strSQLQuery), _variant_t(cn, true), adOpenDynamic, adLockOptimistic, 0)))
                    {
                        rs->MoveFirst();

                        while (!rs->EndOfFile)
                        {
                            // Some codes to operate on the recordset rs
                            ...

                            //  Add record to temp test table 1
                            strSQLQuery = _T("INSERT INTO #TempTable1 VALUES(10000, 1000, 100, 10, 20000, 2000, 200, 20, 99.98, 1920/05/20, 'Hello', 'Hello, World!');");
                            cn->Execute(_bstr_t(strSQLQuery), NULL, 0);    // !!!!!!!!!!!!!!!!!!!Error occurs.

                            rs->MoveNext();
                        }

                        rs->Close();
                    }
                }
            }
        }
        catch (_com_error &e) {
            printf("Description = '%s'\n", (char*) e.Description());
        }
        ::CoUninitialize();
    }

Why?

Now let me explain the reason why I need to use local temporary table:

1. My purpose of using temporary table is to reduce the memory consumption. If using table variable in a stored procedure, then since its data are stored in memory, it cannot implement my goal.

2. Also the data stored in the temporary table are confidential and I do not want any other users to be able to access the data. So a global temporary table or tempdb permanent tables are also not feasible.

3. Due to 1 and 2, temporary local table is the best option for my case. Since in my codes, I connect to SQL Server in the beginning and disconnect in the end, I should be in the same user of the same session. All my operations with the temp tables should be OK. Why the first several SQL statements are OK, but ONLY after I open a recordset, the SQL statement will cause error?

Thank you very much

Answer 1

I highly doubt that the problem has anything to do with temporary tables, at least in terms of being caused by using them or in how you are using them. No errors occur when you create or initially populate either one.

Now that the specific error message has been posted, it seems that there are a few things going on.

First, with regards to the Recordset Object, it seems that it is possible for the Open Method to create a new connection if it cannot determine the active connection. Looking at the Open and Close Methods Example (VC++) MSDN page, I am not sure that you are specifying the ActiveConnection parameter correctly. They are using _variant_t((IDispatch *)pConnection,true) and you are using _variant_t(cn, true). So maybe try _variant_t((IDispatch *)cn,true).

Second, you are passing in a 0 for the Options parameter of Recordset.Open. According to the CommandTypeEnum, you should be using adCmdText which has a value of 1, not 0.

Assuming that fixes the reported problem, you might run into a new problem of trying to execute additional commands on a connection (i.e. session) that is already in the process of sending back a result set. I have not used C++, but would expect that once the connection pointer object, cn, was used to Open() the recordset pointer object, rs, it would be unusable for anything else until you called rs->Close();.

Typically, you could just create another connection pointer object and connect via another session to do the operation. However, since you are using local temporary tables, that second session would not have access to the two temporary tables created via the first connection. One way around this situation is to use Multiple Active Result Sets (MARS). This allows for more than one operation / batch to occur on a single connection. But, there are restrictions and using MARS sometimes complicates things. More info can be found here: Using ADO with SQL Server Native Client.

Another means of doing this type of thing is to combine the INSERT and SELECT into a single statement:

INSERT INTO #TempTable2 (column_list)
  SELECT column_list
  FROM #TempTable1;

Of course, doing this means that you don't need the 2nd loop. If you are not doing any manipulation of the data between reading it and inserting it then that is much faster. But if you do need to manipulate the value in the C++ code, then this is not an option.

You could use global temporary tables (i.e. ##TempTable), which would allow this type of operation to work across two connections, but then if your code ever runs more than once at the same time, you will get name collision errors on the global temporary tables.

OR, since you are not directly JOINing the two local temporary tables together, if you do need to manipulate the data between being read and inserted, you need to create a second connection object as suggested earlier, but in this scenario you would create #TempTable2 via the second connection, not the first connection, so that it exists on the connection that is handling the INSERT.

---

Since your code is clearly just testing out this theory, it is difficult to determine what you are ultimately trying to accomplish. If you can update the Question with details on what the overall goal is, it would be easier to propose an appropriate solution, rather than merely trying to fix this problem, which might end up being an over-complicated means of accomplishing your goal.