Performing Asynchronous Operations in SQL Server Native Client

Article
01/10/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Important

SQL Server Native Client (SNAC) isn't shipped with:

SQL Server 2022 (16.x) and later versions
SQL Server Management Studio 19 and later versions

The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) aren't recommended for new application development.

For new projects, use one of the following drivers:

For SQLNCLI that ships as a component of SQL Server Database Engine (versions 2012 through 2019), see this Support Lifecycle exception.

SQL Server allows applications to perform asynchronous database operations. Asynchronous processing enables methods to return immediately without blocking on the calling thread. This allows much of the power and flexibility of multithreading, without requiring the developer to explicitly create threads or handle synchronization. Applications request asynchronous processing when initializing a database connection, or when initializing the result from the execution of a command.

Opening and Closing a Database Connection

When using the SQL Server Native Client OLE DB provider, applications designed to initialize a data source object asynchronously can set the DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_INIT_ASYNCH property prior to calling IDBInitialize::Initialize. When this property is set, the provider returns immediately from the call to Initialize with either S_OK, if the operation has completed immediately, or DB_S_ASYNCHRONOUS, if the initialization is continuing asynchronously. Applications can query for the IDBAsynchStatus or ISSAsynchStatus interface on the data source object, and then call IDBAsynchStatus::GetStatus orISSAsynchStatus::WaitForAsynchCompletion to get the status of the initialization.

In addition, the SSPROP_ISSAsynchStatus property has been added to the DBPROPSET_SQLSERVERROWSET property set. Providers that support the ISSAsynchStatus interface must implement this property with a value of VARIANT_TRUE.

IDBAsynchStatus::Abort or ISSAsynchStatus::Abort can be called to cancel the asynchronous Initialize call. The consumer must explicitly request Asynchronous Data Source Initialization. Otherwise, IDBInitialize::Initialize does not return until the data source object is completely initialized.

Note

Data source objects used for connection pooling cannot call the ISSAsynchStatus interface in the SQL Server Native Client OLE DB provider. The ISSAsynchStatus interface is not exposed for pooled data source objects.

If an application explicitly forces use of the cursor engine, IOpenRowset::OpenRowset and IMultipleResults::GetResult will not support asynchronous processing.

In addition, the remoting proxy/stub dll (in MDAC 2.8) cannot call the ISSAsynchStatus interface in SQL Server Native Client. The ISSAsynchStatus interface is not exposed through remoting.

Service Components do not support ISSAsynchStatus.

Execution and Rowset Initialization

Applications designed to asynchronously open the result from the execution of a command can set the DBPROPVAL_ASYNCH_INITIALIZE bit in the DBPROP_ROWSET_ASYNCH property. When setting this bit prior to calling IDBInitialize::Initialize, ICommand::Execute, IOpenRowset::OpenRowset or IMultipleResults::GetResult, the riid argument must be set to IID_IDBAsynchStatus, IID_ISSAsynchStatus, or IID_IUnknown.

The method returns immediately with S_OK if the rowset initialization completes immediately, or with DB_S_ASYNCHRONOUS if the rowset continues initializing asynchronously, with ppRowset set to the requested interface on the rowset. For the SQL Server Native Client OLE DB provider, this interface can only be IDBAsynchStatus or ISSAsynchStatus. Until the rowset is fully initialized, this interface behaves as if it were in a suspended state, and calling QueryInterface for interfaces other than IID_IDBAsynchStatus or IID_ISSAsynchStatus may return E_NOINTERFACE. Unless the consumer explicitly requests asynchronous processing, the rowset is initialized synchronously. All requested interfaces are available when IDBAsynchStatus::GetStatus or ISSAsynchStatus::WaitForAsynchCompletion returns with the indication that asynchronous operation is complete. This does not necessarily mean that the rowset is fully populated, but it is complete and fully functional.

If the executed command does not return a rowset, it still returns immediately with an object that supports IDBAsynchStatus.

If you need to get multiple results from asynchronous command execution, you should:

Set the DBPROPVAL_ASYNCH_INITIALIZE bit of the DBPROP_ROWSET_ASYNCH property, before executing the command.
Call ICommand::Execute, and request IMultipleResults.

The IDBAsynchStatus and ISSAsynchStatus interfaces can then be obtained by querying the multiple results interface using QueryInterface.

When the command has finished executing, IMultipleResults can be used as normal, with one exception from the synchronous case: DB_S_ASYNCHRONOUS may be returned, in which case IDBAsynchStatus or ISSAsynchStatus can be used to determine when the operation is complete.

Examples

In the following example, the application calls a non-blocking method, does some other processing, and then returns to process the results. ISSAsynchStatus::WaitForAsynchCompletion waits on the internal event object until the asynchronously executing operation is done or the amount of time specified by dwMilisecTimeOut is passed.

// Set the DBPROPVAL_ASYNCH_INITIALIZE bit in the   
// DBPROP_ROWSET_ASYNCH property before calling Execute().  
  
DBPROPSET CmdPropset[1];  
DBPROP CmdProperties[1];  
  
CmdPropset[0].rgProperties = CmdProperties;  
CmdPropset[0].cProperties = 1;  
CmdPropset[0].guidPropertySet = DBPROPSET_ROWSET;  
  
// Set asynch mode for command.  
CmdProperties[0].dwPropertyID = DBPROP_ROWSET_ASYNCH;  
CmdProperties[0].vValue.vt = VT_I4;  
CmdProperties[0].vValue.lVal = DBPROPVAL_ASYNCH_INITIALIZE;  
CmdProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;  
  
hr = pICommandProps->SetProperties(1, CmdPropset);  
  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_ISSAsynchStatus,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pISSAsynchStatus);  
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   // Do some work here...  
  
   hr = pISSAsynchStatus->WaitForAsynchCompletion(dwMilisecTimeOut);  
   if ( hr == S_OK)  
   {  
      hr = pISSAsynchStatus->QueryInterface(IID_IRowset, (void**)&pRowset);  
      pISSAsynchStatus->Release();  
   }  
}

ISSAsynchStatus::WaitForAsynchCompletion waits on the internal event object until the asynchronously executing operation is done or the dwMilisecTimeOut value is passed.

The following example shows asynchronous processing with multiple result sets:

DBPROP CmdProperties[1];  
  
// Set asynch mode for command.  
CmdProperties[0].dwPropertyID = DBPROP_ROWSET_ASYNCH;  
CmdProperties[0].vValue.vt = VT_I4;  
CmdProperties[0].vValue.lVal = DBPROPVAL_ASYNCH_INITIALIZE;  
  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_IMultipleResults,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pIMultipleResults);  
  
// Use GetResults for ISSAsynchStatus.  
hr = pIMultipleResults->GetResult(IID_ISSAsynchStatus, (void **) &pISSAsynchStatus);  
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   // Do some work here...  
  
   hr = pISSAsynchStatus->WaitForAsynchCompletion(dwMilisecTimeOut);  
   if (hr == S_OK)  
   {  
      hr = pISSAsynchStatus->QueryInterface(IID_IRowset, (void**)&pRowset);  
      pISSAsynchStatus->Release();  
   }  
}

To prevent blocking, the client can check the status of a running asynchronous operation, as in the following example:

// Set the DBPROPVAL_ASYNCH_INITIALIZE bit in the   
// DBPROP_ROWSET_ASYNCH property before calling Execute().  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_ISSAsynchStatus,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pISSAsynchStatus);   
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   do{  
      // Do some work...  
      hr = pISSAsynchStatus->GetStatus(DB_NULL_HCHAPTER, DBASYNCHOP_OPEN, NULL, NULL, &ulAsynchPhase, NULL);  
   }while (DBASYNCHPHASE_COMPLETE != ulAsynchPhase)  
   if SUCCEEDED(hr)  
   {  
      hr = pISSAsynchStatus->QueryInterface(IID_IRowset, (void**)&pRowset);  
   }  
   pIDBAsynchStatus->Release();  
}

The following example demonstrates how you can cancel the currently running asynchronous operation:

// Set the DBPROPVAL_ASYNCH_INITIALIZE bit in the   
// DBPROP_ROWSET_ASYNCH property before calling Execute().  
hr = pICommand->Execute(  
   pUnkOuter,  
   IID_ISSAsynchStatus,  
   pParams,  
   pcRowsAffected,  
   (IUnknown**)&pISSAsynchStatus);  
  
if (hr == DB_S_ASYNCHRONOUS)  
{  
   // Do some work...  
   hr = pISSAsynchStatus->Abort(DB_NULL_HCHAPTER, DBASYNCHOP_OPEN);  
}

Share via

Performing Asynchronous Operations in SQL Server Native Client

Opening and Closing a Database Connection

Execution and Rowset Initialization

Examples

See Also

Feedback

Additional resources