INF: Result Processing for SQL Server

• 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

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