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

This article has been archived. It is offered "as is" and will no longer be updated.
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'.
The HRESULT obtained is:
      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



Note You must change uid=<username> and pwd=<strong password> to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database.
// 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 Coinitializestruct 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
Properties

Article ID: 238116 - Last Review: 01/11/2015 03:01:49 - Revision: 6.0

  • kbnosurvey kbarchive kbdatabase kbprb KB238116
Feedback