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:

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

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:

1. 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
   					

2. Start a new Visual Basic Standard EXE project. Form1 is added by default.
3. From the Project menu, click References, and then select Microsoft ActiveX Data Objects.
4. 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
   					

5. Run the Project.

RESULT: This error occurs: