PRB: ADO to SQL Server Through ODBC Does not Support adAsyncFetchNonBlocking when Using Server-Side Cursors

ID: Q231834

The information in this article applies to:

Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0

SYMPTOMS

If you attempt to use ActiveX Data Objects (ADO) to access SQL Server databases through the ODBC provider (MSDASQL), you will receive the following error if you specify adAsyncFetchNonBlocking with Server-side cursors:

-2147217890 lRowsOffset would position you past either end of the rowset, regardless of the cRows value specified; cRowsObtained is 0.

NOTE: If you are using the SQLOLEDB provider you will not see this error message. However, fetching is still not supported for the cursor and it is blocked until the cursor is fully populated.

CAUSE

The adAsyncFetchNonBlocking recordset option is not supported with a Server cursor. Additional enhancements would be required for the server and client in order to implement this feature. The two way communication necessary to support adAsyncFetchNonBlocking with a server cursor is not available at this time.

RESOLUTION

adAsyncFetchNonBlocking is only supported with a Client cursor.

STATUS

This behavior is by design.

MORE INFORMATION

Paste the following code in the ISQL_w window, and either select a test database or use the pubs database, and then execute the code. This code creates the test table and populates it with data. This sample uses approximately 2.5 meg of 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

The following code demonstrates the problem. Create a new project and then create a new form. Paste the following code into the General Declarations section of the form and add a Project reference to the Microsoft ActiveX Data Objects 2.x Library:


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

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.

REFERENCES

For more information, see the following article in the Microsoft Knowledge Base:

Q190606 PRB: QueryTimeout Event Not Available

Additional query words:

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:5.0,6.0
Platform : WINDOWS
Issue type : kbprb