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

• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.5
• Microsoft OLE DB Provider for SQL Server, versions 7.0, 7.01
• Microsoft SQL Server versions 4.2x, 6.5, 7.0
• Microsoft Visual Basic Learning and Enterprise Editions for Windows, versions 5.0, 6.0

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.

MORE INFORMATION

Steps to Reproduce Behavior

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.

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

REFERENCES

© Microsoft Corporation November 19, 1999, All Rights Reserved.
Contributions by Terrell D. Andrews, Microsoft Corporation

Additional query words:

Keywords : kbADO kbOLEDB kbSQLServ kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbADO250
Version : WINDOWS:2.1,2.1 SP1,2.5,5.0,6.0,7.0,7.01; winnt:4.2x,6.5,7.0
Platform : WINDOWS winnt
Issue type : kbprb