Multi-Step Transaction, fails but reports success

I have for example the following SQL:
BEGIN TRAN
Delete from users where userid = 102
Update mainSettings set userCnt = UserCnt -1
COMMIT TRAN
If for example the "update" statement fails, but the "delete" statement
is successful - the overall transaction reports no error, and thus, my
appliction thinks everything was a success.
Any ideas?
VB 6.0
MDAC ADO 2.7
JasonAnswered in microsoft.public.sqlserver.programming
Please do not multi-post.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||You have to check for @.@.error after every statement that insert / delete or
update
the db. Also, you have to check the return value of the sp.
create procedure proc1
@.userid
as
set nocount on
declare @.error int
BEGIN TRAN
Delete from users where userid = @.userid
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('Error deleting from table users.', 16, 1)
return 1
end
Update mainSettings set userCnt = UserCnt -1
if @.error != 0
begin
rollback transaction
raiserror('Error updating from table [mainSettings].', 16, 1)
return 1
end
COMMIT TRAN
return @.@.error
go
declare @.rv int
declare @.error int
declare @.tc int
set @.tc = @.@.trancount
exec @.rv = proc1 102
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.error != 0
begin
print 'there was an error.'
end
if @.@.trancount != @.tc
rollback transaction
go
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"jroozee@.gmail.com" wrote:
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
server return a error?
Jason|||Read the articles. There you will find the answer.
AMB
"jroozee@.gmail.com" wrote:
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Same reason, last statement in the batch was successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112043813.998362.180420@.g14g2000cwa.googlegroups.com...
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Additionally, the reason you must check for the error, is that the SQL
programmers can not make the decision for you as to whether or not the error
is sufficient to roll back the transaction. That is for you to decide, so
you much catch the errors and rollback the transaction when necessary.. (
There are types of errors which will automatically roll back the transaction
without your intervention. These are generally more severe errors which
cause batch abort, or you have been chosen as a deadlock victim.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
>I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>

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-Instance Cluster

Situation: Two nodes, two nodes are active, two virtual
servers, two separate applications. One active and one
wait on each. Active on each fails over to wait on each
other server so that I should only lose one application at
a time.
Question: Two SQL instances on each node/virtual server?
Each SQL instance has its own IP address? The active and
wait have their own IP addresses?
Ok, so you want to run a 2 node cluster, with both nodes being active at the
same time. Two databases for two applications.
You will only need 1 instance of SQL on a node, unless the application
requires something different.
Each Node will have an IP addr, the cluster will have an addr, and each
instance (1 on each server) will have an IP addr.
With all the said, I never recommend an Active/Active cluster. SQL optimizes
memory. When you have a failure, both instances of SQL and its IP addr will
then be on one box. This will change the way SQL can use and optimize
memory. This process takes time, during that time, the system is much
slower. Performance on most Active/Active SQL Clusters suffer during a
failed state.
If at all possible, run both apps on the same node, at the same time. Making
sure of course, that each system has ample resources.
Hope this helps.
Cheers,
Rod
"Lucian" <anonymous@.discussions.microsoft.com> wrote in message
news:1d2b101c42329$d3d501c0$a401280a@.phx.gbl...
> Situation: Two nodes, two nodes are active, two virtual
> servers, two separate applications. One active and one
> wait on each. Active on each fails over to wait on each
> other server so that I should only lose one application at
> a time.
> Question: Two SQL instances on each node/virtual server?
> Each SQL instance has its own IP address? The active and
> wait have their own IP addresses?
|||With SQL Server 2000 you can install up to 16 instances (1 default and 15
named or 16 named). The number of nodes that can be a possible owner for
each instance depends on your edition/version of Windows. If you have a
typical 2-node cluster, each node can own 0,1, or many of the installed
instances.
Whether it makes sense to install multiple instances (something that used
to be called active/active when you could only have two instances) depends
totally on your environment - your hardware, your application, your
business needs, etc.
If you want to know more about clustering, check out BOL or the MSPress
book on High Availability.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?
Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare one
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare on
e
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/