PRB: Accessing a Temporary SQL Server Table Results in a DB_E_NOTABLE (238116)

MORE INFORMATION

Steps to Reproduce this Error

1. Create an empty WinConsole application.
2. Insert the sample code below into a Visual C++ source code file.
3. Set the appropriate values of Server, User ID, and Password to access the default Pubs database on a SQL 7.0 Server.
4. Compile and run the application.
5. Uncomment either of the following lines:

   // conn->CursorLocation = adUseClient;
   //rs.Release();

Visual C++ 6.0 Sample Code

// Start of TempTbl.cpp
//  Database Type : SQL Server 7
//  Server: "ServerName" UID: <username> PWD: <strong password>
//  This code checks lifetimes of temporary tables in SQL 7.0
//  Database : pubs
//  TableName: #temptable12
// The includes
#include <stdio.h>		// Needed for printf.
#include <tchar.h>		// Needed for _T() macro.
#include <conio.h>		// Needed for _getch().
#include <afxdisp.h>  // CString etc...
// The #import
#undef EOF
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" rename_namespace("ado20")
#define CATCHCOM(hr)  if ( FAILED( hr ) ) throw( _com_error( hr, NULL ) );
// The BSTR's
_bstr_t connStrSQL("Driver=SQL Server;Server=ServerName;Database=pubs;UID=<username>;PWD=<strong password>;");
// The Coinitialize
struct HandleCOM
{
   HandleCOM()  { ::CoInitialize(NULL); }
   ~HandleCOM() { ::CoUninitialize();   }
} _HandleCOM_;

int main(void)
{
   using namespace ado20;
   _ConnectionPtr conn;
   _RecordsetPtr rs;
   _variant_t vra;
   HRESULT hr;
   try
   {
	conn.CreateInstance(__uuidof(Connection));
	hr = conn->Open(connStrSQL,L"",L"",-1);
	//conn->CursorLocation = adUseClient;
	CATCHCOM(hr)
	rs =conn->Execute(_bstr_t("Select * into #temptable12 from authors"),&vra,-1);
	//rs.Release();
	rs = conn->Execute(_bstr_t("Select * from #temptable12"),&vra,-1);
   }
   catch (_com_error &ce)
   {
	CString adoStr,msgStr,tempStr;
	// 
	// Trace COM error information.
	// 
	adoStr=_T("");
	TRACE( "\nCom Exception Information\n-----------------------------------------------\n" );
	TRACE( "Description : %s\n",   (char*) ce.Description()  );
	TRACE( "Message     : %s\n",   (char*) ce.ErrorMessage() );
	TRACE( "HResult     : 0x%08x\n", ce.Error() );
	// 
	// Trace ADO exception information only if connection is not null.
	// 
	if ( NULL != conn )
	{
	   TRACE( "\nADO Exception Information\n-----------------------------------------------\n" );
 	   ado20::ErrorPtr err;
	   for ( long i=0; i<conn->Errors->Count; i++ ) 
	   {
		tempStr=_T("");
		err = conn->Errors->Item[i];
		TRACE( "Number      : 0x%08x\n", err->Number );
		TRACE( "Description : %s\n",	  (char*) err->Description );
		TRACE( "SQLState    : %s\n",     (char*) err->SQLState );
		TRACE( "Source      : %s\n\n",   (char*) err->Source );
		tempStr.Format("Ado Exception :\n===============\nDescription : %s\nSource : %s\n",(char*) err->Description,(char*) err->Source);  
		adoStr += tempStr;
	   } 
	}
	msgStr.Format("Com Exception :\n===============\nDescription : %s\nMessage     : %s\n%s",(char*) ce.Description(),(char*) ce.ErrorMessage(), (LPCTSTR) adoStr);  
	MessageBox(::GetDesktopWindow(),msgStr,"Error Message", MB_OK);
   }
   return 0;
}
// End of TempTbl.cpp