Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2013
    Posts
    19

    Unanswered: Cannot access a temporary table?

    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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The #tempTables are only accessible from the spid (SQL Server process) which created them. The ##tempTables are accessible from any spid on the server. There are ways to work around this by sharing context, etc. but without more insight into what your application needs to do, share, and keep separate I can't offer you dependable guidance.

    For quick and dirty testing, use two pound signs to make the temp tables global. This probably isn't a good long term solution, but it will get you over the hump for the moment.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    19
    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, 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. All my operations with the temp tables should be in one session. Why the first several SQL statements are OK but after opening a recordset, the SQL statement will cause error?

    I have tested my codes by remove the '#' and then everything is OK.

    Thank you very much

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use Visual Studio to step through your code, line by line. Specifically watch to see when new connections are created.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2013
    Posts
    19
    Hi,

    I just do that. But how to know a new connection is created?

    Thanks

    Alan

    Quote Originally Posted by Pat Phelan View Post
    Use Visual Studio to step through your code, line by line. Specifically watch to see when new connections are created.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sorry, but you need more help with ADO than I can give you in the next few days.

    Try to restructure your C code so that it populates the tables first, then retrieves the rows. That should fix things.

    If you have the time and money for training from Pluralsight.com I would strongly suggest it. They offer courses on ADO, C, and many other tools/etc. that will help a lot.

    I'm caught in a time crunch due to the end of the year and vacation schedules (I'm covering a lot of systems right now). I just can't set aside enough hours to do a good job helping you unravel all of this.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2011
    Posts
    3
    Quote Originally Posted by chcw View Post
    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, then since its data are stored in memory, it cannot implement my goal.
    Table variables are stored in TEMPDB not in memory.

    Refer below post to understand Temp Table vs Table Variable
    http://www.itdeveloperzone.com/2015/...le-in-sql.html

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Pretty sure your recordset opens a separate session (spid) on the same connection, so this won't be able to access the same temporary objects.

    What are you trying to achieve? The use of inline SQL and creation of temporary tables seems like a bit of a mistake to me.
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1

    use a table variable

    Instead of using a Temp table, use a table variable, that should solve your problem

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •