INF: Result Processing for SQL Server

This article was previously published under Q165951
This article has been archived. It is offered "as is" and will no longer be updated.
When using Microsoft DB-Library, VBSQL (VBX and OCX), or ODBC to access SQLServer, it is critical that all result sets are completely processed in atimely manner. The result sets need to be processed to avoid problems withsubsequent SQL Server queries and to avoid concurrency issues with SQLServer resources.

In most cases the return code from dbsqlok or dbsqlexec should be ignored.If you send the following batch:
   insert into tblTest values(1)   select @@VERSION				

and the insert statement fails due to a duplicate key, a Sev 14 error isgenerated but the batch continues. THe dbsqlok and dbsqlexec calls onlycheck the success of the first command. If you do not call dbresults youwill not process the select statement results and can get result pendingerrors.
The following are the most common problems your application may encounterif result sets are not handled immediately and completely:
  • If all result sets are not processed completely and you attempt to send another query to SQL Server using the same connection, you will receive the following DB-Library error:
    10038 - Attempt to initiate a new SQL Server operation with results pending.
    DB-Library prevents you from sending additional queries if there are results from a previous query that need to be handled. For more information, see the following article in the Microsoft Knowledge Base:
    117143 : When and How to Use dbcancel() or sqlcancel()
  • If a query is issued to SQL Server and the results are not handled immediately, you may be holding locks and reducing concurrency on your server.

    For example, suppose you issue a query that requires rows from two pages to populate your result set. SQL Server will parse, compile, and run the query. This means that shared locks are placed on the two pages that contain the rows needed to satisfy your query. Further, suppose that not all rows fit onto one SQL Server TDS packet (the method by which the server communicates with the client). TDS packets are filled and sent to the client. If all rows from the first page fit on the TDS packet, SQL Server releases the shared lock on that page, but leaves a shared lock on the second page. SQL Server then waits for the client to request more data (this is done using dbnextrow/dbresults, SQLNextRow/SQLResults, FetchLast/FetchFirst, and so on).

    This means that the shared lock is held until the client requests the rest of the data. Other processes requesting data from the second page may be blocked. For more information, see the following article in the Microsoft Knowledge Base:
    162361 : Understanding and Resolving SQL Server Blocking Problems
Below is an example of how to correctly handle all result sets. This is aC/C++ DB-Library code fragment. The same concept should be applied to VBSQLand ODBC result handling.
   BOOL bMoreResults = TRUE;   BOOL bMoreRows = TRUE;   RETCODE dbRC = SUCCEED;   //    // send query   .   .   .   //    // process *all* results   bMoreResults = TRUE while(bMoreResults)   {      switch(dbRC = dbresults(pdbproc))      {      case SUCCEED:        bMoreRows = TRUE;        while(bMoreRows)        {          switch(dbRC = dbnextrow(pdbproc))          {            case REG_ROW:              // handle regular row              break;            case NO_MORE_ROWS:              bMoreRows = FALSE;  // all rows in this result set handled              break;            case BUF_FULL:              // handle full buffer when using row buffering              break;            case FAIL:              // any error processing desired              bMoreRows = FALSE;              break;            default:              // handle compute row              break;          }        }        break;      case NO_MORE_RESULTS:        bMoreResults = FALSE;  // all result sets handled        break;      case FAIL:        // any error processing desired        // The current command has returned an error        // could be a non fatal error        bMoreResults = TRUE;        break;      case NO_MORE_RPC_RESULTS:        // extract stored procedure return information        break;      default:        bMoreResults = FALSE;  // unknown        break;      }   } // while(bMoreResults && FALSE == DBDEAD(pdbproc))				

Article ID: 165951 - Last Review: 02/24/2014 08:27:57 - Revision: 3.3

  • Microsoft SQL Server Programmer's Toolkit 4.2
  • Microsoft Open Database Connectivity 2.5
  • Microsoft Open Database Connectivity 3.0
  • Microsoft SQL Server 2000 Standard Edition
  • kbnosurvey kbarchive kbbug kbhowto kbprogramming kbusage KB165951