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

Article translations Article translations
Article ID: 238116 - View products that this article applies to.
This article was previously published under Q238116
Expand all | Collapse all

On This Page

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 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

Properties

Article ID: 238116 - Last Review: December 5, 2003 - Revision: 5.1
APPLIES TO
  • Microsoft ODBC Driver for Microsoft SQL Server 3.7, when used with:
    • Microsoft Data Access Components 1.5
    • Microsoft Data Access Components 2.0
    • Microsoft Data Access Components 2.1
    • Microsoft Data Access Components 2.5
    • Microsoft Data Access Components 2.6
  • Microsoft ODBC Driver for Microsoft SQL Server 3.0, when used with:
    • Microsoft Data Access Components 1.5
    • Microsoft Data Access Components 2.0
    • Microsoft Data Access Components 2.1
    • Microsoft Data Access Components 2.5
    • Microsoft Data Access Components 2.6
  • Microsoft ODBC Driver for Microsoft SQL Server 3.5, when used with:
    • Microsoft Data Access Components 1.5
    • Microsoft Data Access Components 2.0
    • Microsoft Data Access Components 2.1
    • Microsoft Data Access Components 2.5
    • Microsoft Data Access Components 2.6
  • Microsoft ODBC Driver for Microsoft SQL Server 3.6, when used with:
    • Microsoft Data Access Components 1.5
    • Microsoft Data Access Components 2.0
    • Microsoft Data Access Components 2.1
    • Microsoft Data Access Components 2.5
    • Microsoft Data Access Components 2.6
  • Microsoft ODBC Driver for Microsoft SQL Server 3.7, when used with:
    • Microsoft Data Access Components 1.5
    • Microsoft Data Access Components 2.0
    • Microsoft Data Access Components 2.1
    • Microsoft Data Access Components 2.5
    • Microsoft Data Access Components 2.6
Keywords: 
kbdatabase kbprb KB238116

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com