INF: Asynchronous Query Execution Using VBSQL

• Microsoft SQL Server, version 4.2

SUMMARY

This article provides the basic information necessary to execute a query asynchronously in a SQL Server front-end using the Microsoft Visual Basic Library for SQL Server (VBSQL).

MORE INFORMATION

Queries sent from a Visual Basic front-end to SQL Server can be sent either synchronously or asynchronously. Synchronous query execution is done by calling SqlExec% to execute the query. The client program will pause execution on the SqlExec% line until the query has been completely processed by the server, and the results returned to the client program.

Because Microsoft Windows is a cooperative multitasking environment, it may be desirable to allow the user to perform other tasks while a long- running query is executing at the SQL Server. This can be accomplished by using asynchronous query execution.

Asynchronous query execution involves using SQLSend% to send the query to SQL Server, looping until SQLDataReady% returns SUCCEED (1), and then calling SqlOk% to verify the correctness of the command batch. Inside of the SqlDataReady% loop, it is necessary to call the Visual Basic function DoEvents() so that other Windows events can be processed.

Below is an example code fragment that illustrates the function calls:

   Result% = SqlCmd%(SQLConn%, Query$)
   Result% = SqlSend%(SQLConn%)
   While SQLDataReady%(SQLConn%) = 0
       dummy% = DoEvents()
   Wend
   Result% = SqlOk%(SQLConn%)

Therefore, it is possible to get into a situation where the SQL Server begins to send data packets back to the client which causes SqlDataReady% to correctly return SUCCEED but more processing must be done at the server, and more data must be sent back to the client before SqlOk% can complete its work and return SUCCEED.

If the above situation occurs, any queries or stored procedures involved would need to be restructured and sent in pieces to avoid a long delay on the call to SqlOk%.

NOTE: The same technique for asynchronous query execution could be used in a Windows application using DB-Library (DB-Lib) for C. The VBSQL function calls would need to be changed to their DB-Library for C equivalents. Also, the call to DoEvents() would need to be replaced with appropriate Windows message-handling code.

For more information on DB-Library programming techniques, see the article "Developing Microsoft Windows-Based Applications for Microsoft SQL Server" in the SQL Server Resource Kit, which is available on the Microsoft Technet CD.