PRB: Accessing a Temporary SQL Server Table Results in a DB_E_NOTABLE

• Microsoft ODBC Driver for SQL Server, versions 3.0, 3.5, 3.6, 3.7, included with:
  • Microsoft Data Access Components versions 1.5, 2.0, 2.1, 2.1 (GA), 2.1 SP1, 2.1 SP2

SYMPTOMS

When using the SQL Server ODBC driver with forward-only server-side cursors in trying to access a local temporary SQL Server table that was explicitly created using the same ActiveX Data Object (ADO) connection object, the application receives the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#temptable12'.

      DB_E_NOTABLE // 0x80040e37 

CAUSE

The SQL Server ODBC driver does not support multiple active recordsets on the same connection when using the forward-only cursor. The OLE DB Provider for ODBC drivers attempts to work around this limitation by creating a second connection. Since temporary tables are only visible to the connection that created it, the application fails to find the table and returns a DB_E_NOTABLE HRESULT.

RESOLUTION

Use one of the following solutions to correct the problem:

• Use ADO client-side cursors.



• Use the Microsoft OLE DB Provider for SQL Server.



• Insert a rs.Release() call in between two execute calls in the Visual C++ code, when using forward-only server-side cursors.

STATUS

This behavior is by design.

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: "sa" PWD:""
//  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=sa;PWD=;");
// 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
 

REFERENCES

SQL Server Books Online

Additional query words:

Keywords : kbDatabase kbMDAC kbODBC kbSQLServ kbODBC300 kbODBC350 kbODBC360 kbGrpVCDB kbGrpMDAC kbMDAC210 kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.1,2.1 (GA),2.1 SP1,2.1 SP2,3.0,3.5,3.6,3.7
Platform : WINDOWS
Issue type : kbprb