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
>

Multiserver Administration Master Server (MSX)

Hi
I am trying to delete some jobs on a SQL Server 2000
database that I have taken over and I am getting the
following error : Error 14274 - Cannot delete a job that
originated from an MSX server.
We only have one server in our organisation and I have
checked and it is not set up as an MSX server.
Please could someone tell me how I can delete these jobs.
Many thanksIt sounds like the server has been renamed at some point. You need to update
sysjobs to reflect the current server name in the originating_server column.
You can use the following procedure to do this
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Philip" <anonymous@.discussions.microsoft.com> wrote in message
news:07c001c3d423$352cfaf0$a301280a@.phx.gbl...
> Hi
> I am trying to delete some jobs on a SQL Server 2000
> database that I have taken over and I am getting the
> following error : Error 14274 - Cannot delete a job that
> originated from an MSX server.
> We only have one server in our organisation and I have
> checked and it is not set up as an MSX server.
> Please could someone tell me how I can delete these jobs.
> Many thanks
>sql

Multiple row delete syntax error

Hello

I am trying to delete multiple rows in my sql 2000 database, I have used the following sysntax but I keep getting errors:

DELETE From NameList

WHERE LName = 'Smith';
and
LName = 'Jones';
and
LName = 'Peters';
and
LName = 'Adams';
and
LName = 'Conner';
and
LName = 'Simon';

I have tried editing the syntax in a variety of ways, but I just can't find the correct solution.

I have Googled and so far not found another syntax format. What am I doing wrong.

Thanks.

Lynn

First, a semi-colon ( ; ) tells SQL Server that is the end of the statement. So you are probably getting incorrect syntax on line 3 above. I think what you want to do is this:

DELETE From NameList
WHERE LName = 'Smith'
OR
LName = 'Jones'
OR
LName = 'Peters'
OR
LName = 'Adams'
OR
LName = 'Conner'
OR
LName = 'Simon';

|||

Try this to work faster

DELETE From NameList
WHERE LName in( 'Smith','Jones','Peters','Adams','Conner','Simon')

and is much shorted and easy to create in .Net

Thanks

|||

Hi Guys

Thanks a lot your syntax works great and so fast too.

You have saved me hours of manually deleting rows. As I also have have many other tables needing rows deleted.

Cheers

Lynn

|||

Hello jpazgier

By the way, before I try this out and perhaps create a problem.

Does your shortened version work to inset data into multiple rows also?

This is the way I have inserted in the past, row by row.:

INSERT INTO LName ( [LName])
VALUES( N'Murphy')

I need to insert multiple rows, will this do the job?

INSERT Into NameList ( [LName])
VALUES (N' 'Smith','Jones','Peters','Adams','Conner','Simon')

Thanks

Lynn

|||

no it will not allow you to insert multiple rows this way, but for delete it will work perfectly

If you would like to use comma delimited values to insert you can create table returned function which will accept your string and generate table for you and next you can use

INSERT Into NameList ( [LName])
SELECT NAME
from dbo.SplitNamesByComma('Smith,Jones,Peters,Adams,Conner,Simon')

You can also loop throw string from comma to comma and insert names one by one inside stored procedure.

Thanks

|||

Hello jpazgier

Thanks for the informaton.

I thought I had found a quick solution to multiple row inserts, I was feeling quite pleased with myself.

Thanks

|||

maybe you can try this for multiple inserts?

create

table #aa(aavarchar(10))
insertinto #aa
SELECT'aa'
UNION
SELECT'bb'
UNION
SELECT'cc'

select*from #aa

So you only have to replace comma by UNION SELECT maybe it will work for you. It will save you some amount of time because it will be single insert.

Thanks