Database: SQL Server 2000 Standard (works fine)
Database: SQL Server 2005 Standard (fails)
Provider: SQLOLEDB

We can update recordset field (database: MS SQL 2000, client side cursor,
adLockOptimistic), regardless of the recordset's source (query or stored
procedure)

The problem begins with SQL 2005. We cannot update recordset field, if the
recordset is a result of stored procedure.

Error Source: Microsoft Cursor Engine
Error Description: Multiple-step operation generated errors. Check each
status value.

Because that's not how you affect data.  A recordset is for retrieving and
presenting data.  If you want to change the data in the database, use a DML
statement (INSERT/UPDATE/DELETE).  Better yet, call a stored procedure that
does that.

A

Dmitriy Shapiro wrote:
What development platform/language?
I'm assuming you've used "SET NOCOUNT ON" in the procedure ....

So have you looped through the connection's Errors collection to see the
error message(s)?

If you are developing for ASP, then I will echo Aaron's suggestion: use DML.

If it's a desktop application, then there are some valid reasons (handling
concurrency, etc.) for using a recordset to perform data maintenance.

If none of the above helps, you should post a repro script (DDL and
recordset code) to the group that is focussed on your development platform.

Thanks for replies. Our firewall block all notifications. Sorry.

The changes in recordset are not going back to the database. They are only
for UI. This is a legasy code.

Language: VB 6.0
Platform: Windows XP and Win 2003

Show quoteHide quote

Dmitriy Shapiro wrote:
?? Data is trnasmitted but error messages are blocked? I don't think this is
possible.

I'm not sure I understand what you are saying here, or why it is relevant
that the code is legacy.

So you plan to follow up in a VB group ... ?
Try microsoft.public.vb.database or microsoft.public.vb.database.ado.

Also, you might try using SQL Profiler to trace the actual commands being
sent to the database by the application: it may provide a clue.

Bob Barrows

Here is an example:
'get recordset from database as result of "stored_procedure"
'GetData executes ADO command, with Client Side Cursor and optimistic
locking option
Set rs = GetData("stored_procedure")

'translate column1 to Spanish
'Our legasy code use this technique to translate some data, before
presenting them to the client
rs("column1").value = trnalsateToSpanish(rs("column1").value)

'show recordset in the grid
Display(rs)

These lines work fine when we connect to SQL Server 2000

They fail with SQL Server 2005

I would like to find solution that will have minimum impact on the code.



Show quoteHide quote

Dmitriy Shapiro wrote:
<snip>
Show quoteHide quote

Dmitriy Shapiro wrote:
Unless you have set the connection's CursorLocation property to adUseClient,
this line will result in a default server-side forward-only cursor.

To have control over the cursor type, you must:

Set rs= New ADODB.Recordset
rs.CursorLocation=adUseClient

Then either use the command object as the source argument in the recordset's
Open method:

rs.Open cmd

or use the stored-procedure-as-connection-method technique to bypass the
creation of the explicit Command object:

cn.stored_procedure parm1,...parmN, rs

Bob Barrows

Bob,

I do have MyDBConnection.CursorLocation = adUseClient

Before I was unable to update recordset regardless if was result of stored
procedure or query.
Now I can only do it if it result of the query.

This code works:
Set rs = GetData("select column1 from table1")
rs("column1").value = "abc"

And this code does not work with new SQL Server 2005, but works with SQL
Server 2000:
Set rs = GetData("stored_procedure")

rs("column1").value = "abc"

Thank you.

Show quoteHide quote

Dmitriy Shapiro wrote:
Show quoteHide quoteI'm sorry, but without knowing what the GetData function and the stored
procedure look like, nobody will be able to help you. An I'm sure you are
going to be able to get more help from thhe VB experts in one of the VB
newsgroups.

Thank you.
Show quoteHide quote

[posted and mailed]

Since Dmitriy was told to go somewhere else, I send him a Cc to invite
him back to the thread.

Dmitriy Shapiro (dshap***@veramark.com) writes:
And you do disconnect your recordset, don't you?

I have been able to reproduce the scenario, although I am not sure how
to interpret the results. To wit, the attached VB project permits you
run one of three queries:

1) Runs a stored procedure (created in the program) agains the Orders
   table in Northwind.
2) The same select on Orders.
3) sp_who

The VB program the cuts down the status column in the recordset to three
characters.

If I run this against SQL 2000, it works for all three.

If I run this against SQL 2005, it works for 1 and 2, but fails for
sp_who. I failed to include a direct SELECT * FROM sysprocesses, but
I did test this, and this too failed on SQL 2005, but worked on SQL 2000.
So it does not really seem to be an issue of SP vs. direct query.

If I use the MSDASQL provider (OLE DB over ODBC), then all three works.

Exactly what is going on, I cannot say. It seems that SQL 2005 returns
metadata differently that causes the OLE DB provider to be confused.
But I don't know whether SQL 2005 breaks the specification, or if the
problem is client-side. When testing this, I found several ways of getting
dreaded multi-step error on SQL 2000 as well.

I considered to submit a bug on
http://lab.msdn.microsoft.com/ProductFeedback/, but as long as I can
only repro the issue with a result set from sp_who, it seems to me that
I have a thin case.

I offer the VB project as something that you and others and play with,
and possible further refine. With a more realistic repro it could be an
idea to submit a bug.

Of course, if this proves to be a bug that needs to be fixed, you would
have to open a case with Microsoft, to get a fix in due time. Then again,
if you can get your application to work if you use MSDADQL, that may
be sufficient for you.

Erland Sommarskog wrote:
I would dispute the word "told" - I based my "suggestion" to try another
group on the fact that nobody else in this group had responded up to that
point.

Bob Barrows

Hi!

I have the exact same error using shaped recordsets in my application,
and trying to run it with SQL Server 2005.

It is a VB6 client, it is working when I run it against SQL Server 2000,
but not against SQL Server 2005.

I get the error: -2147217887 Multiple-step operation generated errors.
Check each status value.

The error occurrs as soon as I try to assign a value to a field in a
"sub-recordset". I can assign and save values to the "top" (or "Main")
recordset, but not to existing or newly added fields in a subrecordset.

MSDASQL does not support the SHAPE keyword, does it?

It would be great if someone had a solution. The application can't be
rewritten right now (to use something els than hierachical recordsets).

Thanks,
Andreas Jansson

*** Sent via Developersdex http://www.developersdex.com ***

Andreas Jansson (nospam@devdex.com) writes:
Exact same error? Well, "Multiple-step..." can be about anything.

One case where I think I had this recently was when I tried to cram
in more characters in a field than what was in the underlying
table column.

Nor does SQLOLEDB. If you are into SHAPE, you are using the MS Data
Shape provider. Whether it supports MSDASQL, I don't know, but I don't
really see why it shouldn't.

Without seeing any code, neither VB nor the SQL code, it is very difficult
to give much suggestions. If you can present some code that demonstrates
the problem, it's easier to have a look. It would need the tables and
some sample data as well.

Hi!

I've been debugging and looking to isolate the problem to be able to present
a reproducable case:

Now, at last, I have isolated the problem and wasn't the SHAPE command that
raised the error, but a UNION SELECT in a query of one of my subrecordsets.
(The provider made no difference either.)

Knowing that the UNION keyword was the source, the bug/error was easily
reproduced. I used the Pubs database (I restored a copy of it into the SQL
Server 2005 as well, since I did not have it there).

Here is a piece of code, runnable as a VB script (you could also uncomment
the type declarations and paste it into a method in VB6):

  Dim Sql 'As String
  Dim cn 'As ADODB.Connection
  Dim rs 'As ADODB.Recordset
  'Set cn = New ADODB.Connection
  Set cn = CreateObject("ADODB.Connection")

  ' Exchange the server name and login data!
  cn.Open "Provider=SQLOLEDB;Data Source=MYSERVER;Initial
Catalog=Pubs;Trusted_Connection=no;user id=sa; password=MYPASSWORD"

  ' Build the SQL String to execute - a simple union, from the Pubs (default
SQL 2000) database.
  Sql = "SELECT au_id, au_fname FROM authors WHERE au_id = '213-46-8915' "
  Sql = Sql & " UNION SELECT au_id, au_fname FROM authors WHERE au_id >
'213-46-8915'"

  ' Create, open and disconnect the recordset
  Set rs = CreateObject("ADODB.Recordset")
  rs.CursorLocation = 3 'adUseClient
  ' On the next row; 3 = adOpenStatic, 4 = adLockBatchOptimistic
  rs.Open Sql, cn, 3, 4
  Set rs.ActiveConnection = Nothing

  ' Try to assign a value to the au_fname field. This row generates an error
when the
  ' recordset is fetched from a SQL Server 2005 database, but works with SQL
Server 2000.
  rs.Fields("au_fname").Value = "Andreas"

  ' Close and deallocate objects
  rs.Close
  Set rs = Nothing
  If cn.State <> 0 Then cn.Close '0 = adStateClosed
  Set cn = Nothing

I have tested it in VB6, with different ADO versions referenced, but no
difference. The multiple step error occurrs when assigning data to a field
fetched from SQL Server 2005, but not when it is fetched from SQL Server
2000. Removeing the UNION part of the SELECT query makes the field value
assignment work.

My database procedures make use of UNION SELECTs to a great extent, so it
would be good to know if there are other people with the same problem, and if
they (you) can reproduce it in another envronment than mine. (The 2005 server
in my development environment is not part of the same domain, but I doubt
that can be an issue here).

Thanks,
Andreas

Andreas1974 (Andreas1***@discussions.microsoft.com) writes:
Great! I will certainly look into that. Unfortunately, the hour is
getting late, so it will not be today.

Andreas1974 (Andreas1***@discussions.microsoft.com) writes:
The underlying problem is that SQL 2005 does not return metadata for
the underlying table. That makes ADO think that the column is not
updateable. (Which, if you ask me, is a completely stupid idea.)

Since this is a breaking behaviour from SQL 2000, this is a bug in
my opinion. Therefore I have filed
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=F
DBK46226
you can vote for it you like.

The workaround I can think of is to bounce data over a temp table. This
is OK, if you are writing back the updated data through a stored procedure.
If you rely on .Update, that takes you nowhere.

While I have filed a bug report for it, does not mean that it is going
to be fixed any time soon. If this is critical for your business you
should open a case with Microsoft on this, and then try to work to get
a hotfix.

Since there could be some fine detail that you and I have missed, you
may want to wait a week or so, to see if the bug report gets any
resolution.

The MS guy that answered the bug report seemed to think that our application
made use of the PUBS database, and that the sample UNION SELECT was somthing
that was part of the production code... giving suggestions on how to improve
the specific sample code :-)

If I remember correctly, you would get an error in SQL2000 as well, when
calling the .UpdateBatch method of the recordset if the recordset did not
carry all information that it needed. Now the error occurrs in the client
instead. I wonder why they didn't stick with the old behaviour, raising the
error only for automatic updates and only the moment when the recordset is
"reattatched" to the database.

Andreas

Andreas1974 (Andreas1***@discussions.microsoft.com) writes:
Yes, that part of the response was not very good. However, while I indeed
had an impulse to make a comment about it, I did not find it worth to
reopen the bug only because of that.

After all the resolution is very clear - this is a deliberate change. I
think we will have a hard time to convince the SQL Server team to change
this. I think your best argument would be "if you don't change this back
this many customers will not upgade to SQL 2005". Then again, since MS
probably want you to move to .Net, not even that argument is not likely
to be persuavise.

The problem with .UpdateBatch must have been some other variation. I
recognize the problem from our code, where we have to bounce data over
a temp table when a column in a result set is computed.

I would also say that the root problem is not with SQL Server but
ADO Classic, a client API I don't hold in high esteem. ADO should
let you change any field of your recordset, nevermind that you can't
do .Update or .UpdateBatch on it. You should not do that anyway, but
use stored procedures.

By the way, I've sent out a question to some of our developers to see
if they have a feeling if this could concern us. So I like to thank you
for taking the time to produce a repro and post it here. Sorry, that
I could help you to a painless solution.

Application - document locking
Ok, time to start a war
Replacement of C code with C# code
Case statement 'Operator' in Where clause
How to format a SELECT resultset as a comma separated list?