December 26, 2015 at 8:09 pm
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'", (char*) e.Description());
}
::CoUninitialize();
}
Why?
Thanks
December 26, 2015 at 10:35 pm
Temporary tables have session-level scope. A new connection will be unable to see temporary objects created in another connection. It is possible to use global temporary tables, but I would tend to discourage uses of global temporary tables even more than I discourage uses of temporary tables.
January 5, 2016 at 1:43 am
It looks as if only one connection is being used. When does the error occur; on table creation, row insert or the select?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply