Microsoft KB Archive/306388

= PRB: CursorType Returns adOpenStatic from Server-Side Recordset and Behaves Like a Forward-Only Recordset =

Article ID: 306388

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft SQL Server 2000 Service Pack 3a
 * Microsoft SQL Server 2000 Service Pack 1

-



This article was previously published under Q306388



SYMPTOMS
If you request a static, server-side cursor from a SQL stored procedure, you cannot use MovePrevious or MoveFirst to move backward through the recordset. The cursor exhibits the behavior of a forward-only cursor.

NOTE: This problem only occurs if there are user-defined variables in the stored procedure or SQL commands such as SET NOCOUNT ON.

After you run MovePrevious or MoveFirst, you receive the following error message:

Run-time error '-2147217884(80040e24)';

Rowset does not support fetching backward.



Steps to Reproduce Behavior
 Create a Data Source Name (DSN) named SQLPUBS that points to the SQL Server Pubs database. In the SQL Server Enterprise Manager, modify the ByRoyalty stored procedure. Add SET NOCOUNT ON immediately following the AS portion of the stored procedure, and save the changes.

NOTE: This step is important to reproduce the problem. Create a new Visual Basic Standard EXE project. From the Project menu, click References, and then select the Microsoft Data Access Objects 2.x Library check box.  Paste the following code in the form load event: Dim rs As ADODB.Recordset Dim comm As ADODB.Command Dim oCon As New ADODB.Connection Dim prm As ADODB.Parameter Dim StrOut As String StrOut = &quot;&quot; oCon.Open &quot;DSN=SQLPUBS;uid=sa;password=Password1;&quot; Set comm = New ADODB.Command Set comm.ActiveConnection = oCon comm.CommandText = &quot;byroyalty&quot; Set prm = comm.CreateParameter(&quot;@percentage&quot;, adInteger, adParamInput,, 25) comm.Parameters.Append prm Set rs = New ADODB.Recordset 'rs.CursorLocation = adUseClient rs.CursorLocation = adUseServer rs.Open comm,, adOpenStatic, adLockBatchOptimistic 'rs.Open &quot;Select * from authors&quot;, oCon, adOpenKeyset, adLockBatchOptimistic Debug.Print &quot;rs.CursorType = &quot; & rs.CursorType rs.MoveNext rs.MovePrevious ' ERROR OCCURS HERE. 

