Multiple-step OLE DB operation generated errors.

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.> The problem begins with SQL 2005. We cannot update recordset field, if the
> recordset is a result of stored procedure.
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:
> Database: SQL Server 2000 Standard (works fine)
> Database: SQL Server 2005 Standard (fails)
> Provider: SQLOLEDB
What development platform/language?
> 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.
I'm assuming you've used "SET NOCOUNT ON" in the procedure ...

> Error Source: Microsoft Cursor Engine
> Error Description: Multiple-step operation generated errors. Check
> each status value.
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.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||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
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:ef4gCUrIGHA.3408@.TK2MSFTNGP12.phx.gbl...
> 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.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
> Thanks for replies. Our firewall block all notifications. Sorry.
' Data is trnasmitted but error messages are blocked? I don't think this is
possible.

> The changes in recordset are not going back to the database. They are
> only for UI. This is a legasy code.
I'm not sure I understand what you are saying here, or why it is relevant
that the code is legacy.

> Language: VB 6.0
> Platform: Windows XP and Win 2003
>
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
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> I'm not sure I understand what you are saying here
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

>why it is relevant
> that the code is legacy.
I would like to find solution that will have minimum impact on the code.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OXigYR1IGHA.2704@.TK2MSFTNGP15.phx.gbl...
> 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
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
<snip>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Dmitriy Shapiro wrote:
> 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")
>
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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Bob,

>Unless you have set the connection's CursorLocation property to
>adUseClient,
> this line will result in a default server-side forward-only cursor.
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.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:epIvE21IGHA.208@.tk2msftngp13.phx.gbl...
> 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
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Dmitriy Shapiro wrote:
> 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")
>
I'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.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

Multiple-step OLE DB operation generated errors

Hello....

I have two linked server... (ServerB and ServerC) which reside on ServerA. I am able to connect to the remote database using "Select" statements without any issues.

When I run this query, It is successful:

delete [SERVERB].MyDatabase.dbo.TableName
from [SERVERB].MyDatabase.dbo.TableName t1
Left join MyDatabase.dbo.TableName t2 on ( t1.ID = t2.ID and
t1.EmployeeNumber = t2.EmployeeNumber and
t1.AccountNumber = t2.AccountNumber)
where t2.ID is null;

However, when I change [SERVERB] to [SERVERC], I receive two errors:

"Could not find server 'ELEARN-FRM-BETA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

And

OLE DB provider "SQLNCLI" for linked server "ELEARN-FRM-BETA" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

When I run profiler on ServerC, I see traffic... mainly a whole bunch of exec "sp_cursorfetch" operations, so I know the connection is valid.

Any ideas?

Forch

Sorry... I forgot to mention that all three servers are SQL Server 2005 x64 edition with SP1.

Forch

sql

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

Multiple step OLEDB Error

The error message is

-2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

It is hard to pinpoint the exact problem w/o any additional info other than just the error msg here.

The error msg itself basically indicates that the application program was passing in an OLEDB property set, one or more of the properties were causing problem. What you should do is to enumerate through the property set and examine the corresponding status value, the error status should shed light on what went wrong.

Multiple Step OLE DB error from VB

One of our users is getting this error message.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21) Multiple-step OLE DB operation
generated errors. Check each OLE DB status value, if available. No work was done.

This is the only user that is currently getting the message. Last week another user got this one day and then it stopped.

Inside VB6 code, we're sending some filters to an ASP that in turn sends a SELECT based on those parameters the SQL database and we get data back to the program.

Stepped through the code and found the error returns after this line

oSqlServConn.Open "Provider=MSDAOSP;Data Source=MSXML2.DSOControl.2.6"

Any thoughts out in ForumLand? Please take it easy on me since I didn't write most of this code. Just trying to solve the problem. :)Check if this (http://support.microsoft.com/default.aspx?scid=kb;en-us;312288) pertains to your situation.|||Nope. Unfortunately not. Only sending a handful of filters and the user has Service Pack 4 already. Wouldn't be as concerned (since this is a user that rarely needs the data and can get it via other means) except this person has the newest computer on the block.|||Still having this problem. Any thoughts out there?|||Check tables names and columns names in SQL. I think there may be issue in deploying columns referred by the page.

Check the following :
Use client-side cursors (by setting the ADO recordset Cursor Location property to adUseClient).
Remove the insert trigger from the source table.
Put SET NOCOUNT ON line at the beginning of the trigger code.
After calling Update the first time to insert the new row in the table, scroll the recordset using methods like MoveNext and MovePrevious before calling Update again.

HTH|||Setting cursor location may affect the situation, but the rest simply does not apply, - the error occurs at the time of initializing the connection, not at the time of execution of an action query that fires a trigger.|||Tried setting cursor location on either side of the equation and still no go. I can recreate the problem on my computer now as long as I'm pointing to a test SQL server. When I switch over and point to our Production SQL server, I get no error.