Microsoft KB Archive/238116

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

Article ID: 238116

Article Last Modified on 12/5/2003

-

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</li></ul>

 Microsoft Data Access Components 2.6</li></ul> </li> Microsoft ODBC Driver for Microsoft SQL Server 3.0, when used with:  Microsoft Data Access Components 1.5</li></ul>

 Microsoft Data Access Components 2.0</li></ul>

 Microsoft Data Access Components 2.1</li></ul>

 Microsoft Data Access Components 2.5</li></ul>

 Microsoft Data Access Components 2.6</li></ul> </li> Microsoft ODBC Driver for Microsoft SQL Server 3.5, when used with:  Microsoft Data Access Components 1.5</li></ul>

<ul> <li>Microsoft Data Access Components 2.0</li></ul>

<ul> <li>Microsoft Data Access Components 2.1</li></ul>

<ul> <li>Microsoft Data Access Components 2.5</li></ul>

<ul> <li>Microsoft Data Access Components 2.6</li></ul> </li> <li>Microsoft ODBC Driver for Microsoft SQL Server 3.6, when used with: <ul> <li>Microsoft Data Access Components 1.5</li></ul>

<ul> <li>Microsoft Data Access Components 2.0</li></ul>

<ul> <li>Microsoft Data Access Components 2.1</li></ul>

<ul> <li>Microsoft Data Access Components 2.5</li></ul>

<ul> <li>Microsoft Data Access Components 2.6</li></ul> </li> <li>Microsoft ODBC Driver for Microsoft SQL Server 3.7, when used with: <ul> <li>Microsoft Data Access Components 1.5</li></ul>

<ul> <li>Microsoft Data Access Components 2.0</li></ul>

<ul> <li>Microsoft Data Access Components 2.1</li></ul>

<ul> <li>Microsoft Data Access Components 2.5</li></ul>

<ul> <li>Microsoft Data Access Components 2.6</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q238116

<div class="symptoms_section">

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: <pre class="fixed_text">     DB_E_NOTABLE // 0x80040e37

<div class="cause_section">

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.

<div class="resolution_section">

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.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce this Error
<ol> <li>Create an empty WinConsole application.</li> <li>Insert the sample code below into a Visual C++ source code file.</li> <li>Set the appropriate values of Server, User ID, and Password to access the default Pubs database on a SQL 7.0 Server.</li> <li>Compile and run the application.</li> <li> Uncomment either of the following lines: // conn->CursorLocation = adUseClient; //rs.Release; </li></ol>

Visual C++ 6.0 Sample Code
Note You must change uid= and pwd= 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: PWD: // This code checks lifetimes of temporary tables in SQL 7.0 // Database : pubs // TableName: #temptable12 // The includes // The #import // The BSTR's _bstr_t connStrSQL("Driver=SQL Server;Server=ServerName;Database=pubs;UID= ;PWD= ;"); // The Coinitialize struct HandleCOM {  HandleCOM  { ::CoInitialize(NULL); } ~HandleCOM { ::CoUninitialize;  } } _HandleCOM_;
 * 1) include <stdio.h>       // Needed for printf.
 * 2) include <tchar.h>       // Needed for _T macro.
 * 3) include <conio.h>       // Needed for _getch.
 * 4) include <afxdisp.h> // CString etc...
 * 1) undef EOF
 * 2) import "C:\Program Files\Common Files\System\ADO\msado15.dll" rename_namespace("ado20")
 * 3) define CATCHCOM(hr) if ( FAILED( hr ) ) throw( _com_error( hr, NULL ) );

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

<div class="references_section">