Microsoft KB Archive/117143

= INFO: When and How to Use dbcancel or sqlcancel =

Article ID: 117143

Article Last Modified on 2/22/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q117143



SUMMARY
The function dbcancel and sqlcancel, the Visual Basic SQL (VBSQL) equivalent, are often used in applications when they should not be. A large percentage of common DB-Library (DB-Lib) or VBSQL programming problems stem from applications that misuse this API call. This article gives some practical guidelines on when and when not to use dbcancel and Sqlcancel.



MORE INFORMATION
It is generally recommended and considered good DB-Library programming practice to processes all results until there are no more results and all rows until there are no more rows when retrieving results or after sending a Transact-SQL batch to the server.

A DB-Library program should never have calls to dbresults/sqlresults and dbnextrow/sqlnextrow hard coded to a predetermined number of iterations.

For example, you should call dbresults and dbnextrow like this:

while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS) {            while (dbnextrow(dbproc) != NO_MORE_ROWS) }

Not calling dbresults and dbnextrow as above can, and often does, cause application problems that may not become evident until later in development or testing when corrective action is costly.

If for some reason an application is required to access only x rows from a result set, it is recommend that the transact SQL command "set rowcount x" be used instead of calling dbnextrow x times and then dbcancel. There are several reasons for not using dbcancel as part of standard results processing routines.

When Not to Use dbcancel
dbcancel will not cancel, rollback, or commit a user-defined transaction. All exclusive locks acquired within the user-defined transaction will be retained even after issuing dbcancel, since the user-defined transaction is still active. This can cause blocking and other difficult concurrency problems. When issuing dbcancel within a user-defined transaction, the programmer should ensure that the transaction is either explicitly committed or rolled back.

Furthermore when operating in this context, it is easy to miss that an application is actually in the midst of a user-defined transaction that was never committed and/or rolled back. In addition, there are also some variations of how dbcancel or the attention signal that it generates are implemented from platform to platform.

NOTE: Microsoft SQL Server implementations of dbcancel are constant across all Microsoft SQL Server platforms such as Windows NT x86, Alpha, and RISC platforms. Compatibility issues arise primarily between different Sybase and Microsoft SQL Server implementations. Also, these differences often stem from transport-related issues such as how one vendor's TCP/IP out-of-band data is implemented and how it interacts with another vendor's TCP/IP out-of-band data implementation, the net results of which can cause portability problems in DB-Library applications using dbcancel.

When to Use dbcancel
dbcancel should be used in cases where the user needs to regain control of an application. In this environment, a programmer can handle the exception of a user issuing a dbcancel in an environment where dbcancel does not work by responding with a message like "dbcancel not supported on this platform!". A programmer may also want to use dbcancel in case of a DB-Library command failure as part of a clean up procedure for the DB-Lib processes effected.

Additional query words: 4.20a oob begin tran end dblib

Keywords: kbinfo kbprogramming KB117143

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.