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"
Back to the top
SQL Server cannot provide schema information necessary for updates if the stored procedure contains more than just a SELECT statement.
Back to the top
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.
Back to the top
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:
Run-time Error '3251': Provider Does Not Support This Operation.
Back to the top
