Cannot access a temporary table?

1

Hi,

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 object xxx is invalid” error. 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)

                {

// 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?

Thanks

2

Maybe You'd better to use (##) instead of (#) for creating temp table . It's because when you create the sharp table with (#) , It just will be accessible by the same user (creator) in the same session .

3

Yes, it will be accessible to the "same user" in the same session but, if you use "##", remember that it creates a GLOBAL Temporary Table visible to all users in all sessions and may destroy any hopes of concurrency. The best thing to do would be to stop doing in managed code that which is more easily and appropriately done in a stored procedure. After all (play on words), "Just because you can do something in the front end, doesn't mean you should."

4

Let
me explain the reason why I need to use 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,
    the SQL statement will cause error?

Thank you very much

5

If not mistaken, Execute() will starts a new session. Check the session ID with @@spid. The value is the same if it is the same session.

select @@spid

Best way to do do what you want in a Stored Procedure as @JeffModen suggested.

6

Hi,

Thank you very much.

I will check this. One question is since each Execute will start a new session, why all my previous invokes of Executes will succeed? They are separate Executes.

7

I am not sure does it really starts a new session. I might have mix it up with something else ... Please do verify

8

When you open the record set using the statement quoted above, the second parameter causes a copy of the connection object (not a copy of the connection string) to be used. That is a new connection, and hence a new session. What you want to do is to pass a pointer or reference to the connection rather than a copy.

You can use the @@spid that @khtan suggested to verify that this is true.

BTW, both temp tables and table variables are stored in tempdb.

9

JamesK:

If I am correct, cn is already a smart pointer which refers to the connection to the SQL Server, I try to do as follows:

SUCCEEDED(rs->Open(bstrt(strSQLQuery), variantt((IDispatch *)cn, true),adOpenDynamic, adLockOptimistic, 0)

But the result is the same.

Thank you very much