Microsoft KB Archive/246636

= PRB: Multiple Statements in Stored Procedure Causes Forward-Only/Read-Only Recordsets =

Article ID: 246636

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft OLE DB Provider for SQL Server 7.01
 * Microsoft Visual Basic 5.0 Learning Edition
 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition

-



This article was previously published under Q246636



SYMPTOMS
Using ActiveX Data Objects (ADO) and the Microsoft OLEDB Provider or ODBC Driver for SQL Server, to retrieve a recordset from a stored procedure using Server-side Cursors, returns a Forward-only/Read-only recordset if that stored procedure contains anything other than a single SELECT statement. When the recordset is edited, the following run-time error occurs:

3251 "Provider Does Not Support This Operation"



CAUSE
SQL Server cannot provide schema information necessary for updates if the stored procedure contains more than just a SELECT statement.



RESOLUTION
You can use client-side cursors in order to get a read-only Static cursor, which allows scrolling and bookmarks. You will have to manage updates yourself.



Steps to Reproduce Behavior
Use these steps to reproduce the problem:   Create the following Stored Procedure in SQL Server 7.0 under the Pubs database. CREATE procedure Test @f int as if @f = 2 begin Select *  from authors End  Start a new Visual Basic Standard EXE project. Form1 is added by default. From the Project menu, click References, and then select Microsoft ActiveX Data Objects.  Place the following code in Form1: Private Sub Form_Load Dim strSQL As String Dim cn as ADODB.Connection Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection Set rst = New ADODB.Recordset

cn.ConnectionString = "Provider=SQLOLEDB;Data Source=Server;User ID=UserName;Password=Password;Initial Catalog=pubs" cn.Open

strSQL = "exec Test 2" rst.Open strSQL, cn, adOpenKeyset, adLockBatchOptimistic, adCmdText

rst("au_lname") = "xxxxx" 'Error Occurs Here rst.UpdateBatch rst.MoveNext End Sub  Run the Project.</ol>

RESULT: This error occurs:

Run-time Error '3251': Provider Does Not Support This Operation.

Keywords: kbstoredproc kbprb KB246636

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.