INF: Result Processing for SQL Server (165951)

SUMMARY

When using Microsoft DB-Library, VBSQL (VBX and OCX), or ODBC to access SQL Server, it is critical that all result sets are completely processed in a timely manner. The result sets need to be processed to avoid problems with subsequent SQL Server queries and to avoid concurrency issues with SQL Server 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 is generated but the batch continues. THe dbsqlok and dbsqlexec calls only check the success of the first command. If you do not call dbresults you will not process the select statement results and can get result pending errors.

MORE INFORMATION

The following are the most common problems your application may encounter if 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 a C/C++ DB-Library code fragment. The same concept should be applied to VBSQL and 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))