PRB: ADO to SQL Server Through ODBC Does Not Support adAsyncFetchNonBlocking When You Use Server-Side Cursors (231834)

MORE INFORMATION

Step to Reproduce Behavior

1. Paste the following code into SQL Server's ISQL_w or Query Analyzer Utility. Select a database with at least 2.5 MB of free space and execute the code. This code creates the test table and populates it with data.

   create table adoAsyncTest
   (
   	col1 int identity(1,1) primary key,
   	col2 varchar(255),
   	col3 varchar(255)
   )
   go
   set nocount on
   declare @iCnt int
   select @iCnt=1
   while @iCnt < 5001
   begin
   	insert into adoAsyncTest values('my test data col1', 'my test data col2')
   	select @iCnt = @iCnt + 1
   end
   					

2. Open Visual Basic and create a new Standard EXE Project. Form1 is created by default.
3. Add a reference to the Microsoft ActiveX Data Objects 2.x Library.
4. Paste the following code into the General Declarations section of the form:

   Option Explicit
   
   Private WithEvents adoCn As ADODB.Connection
   Private WithEvents adoRs As ADODB.Recordset
   Private adoErr As Errors
   
   Private Sub Form_Load()
   
   Dim strConnect As String
   strConnect = "Driver={SQL Server};Server=<ServerName>;User ID=<UserID>;Password=<Password>;Initial Catalog=<Database>"
   
   Set adoCn = New ADODB.Connection
   With adoCn
       '.Provider = "SQLOLEDB"
       .ConnectionString = strConnect
       .Open
   End With
   
   Set adoRs = New ADODB.Recordset
   With adoRs
       Set .ActiveConnection = adoCn
       .CursorLocation = adUseServer 'adUseClient
       .CursorType = adOpenStatic
       .LockType = adLockReadOnly
       .Open "SELECT * FROM adoAsyncTest", , , , (adCmdText + adAsyncFetchNonBlocking)
   End With
   
   Debug.Print "Executing Async..."
   
   End Sub
   
   Private Sub adoCn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
   
   Debug.Print "---------Execute Complete---------"
   Debug.Print "RecordCount = " & adoRs.RecordCount
   
   End Sub
   
   Private Sub adoRs_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
   
   Debug.Print "---------Fetch Complete---------"
   
   End Sub
   
   Private Sub adoRs_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
   
   Debug.Print "Fetch Progress = " & pRecordset.RecordCount
   
   End Sub
   					

5. Run the project. Do not step through the project, as stepping will change the behavior.
6. Change the CursorLocation to adUseClient to resolve the error.

NOTE: You can use adAsyncExecute with a Server cursor, which enhances the responsiveness of your application. Using adAsyncExecute enables your application to perform other tasks while waiting on the server cursor to populate. However, you will not be able to use the recordset until an ExecuteComplete event fires, which indicates the server cursor is fully populated. With a Client cursor, you get more flexibility in async mode and you can check the Fetch progress of the cursor population as the rows are fetched to the client cursor.