March 15, 2005 at 8:46 am
This is killing me. Please help.
"select @@options & 13684" yields 0 which tells me xact_abort is off. Why then, when I execute:
exec spfoo (40) -- definition at bottom
does the procedure throw a fatal error at the first insert and not complete the rest of the code? I thought that by default, constraint violations are not fatal, and so, subsequent code would be executed?
create procedure spfoo (@a int)
as
SET XACT_ABORT OFF
SET IMPLICIT_TRANSACTIONS OFF
declare @localerr int
set @localerr = 0
insert into foo (a, b)
values (@a, 'spfoos')
set @localerr = @@ERROR
insert into foo (a, b)
values (1, 'you should see this!')
return 0
go
March 15, 2005 at 12:17 pm
Can you provide the table definition for "foo"? I want to ensure that I am exactly duplicating the test case.
Thanks
Wayne
March 15, 2005 at 12:26 pm
Happily Wayne. Thanks for the response. Some of the spfoo code may have changed as I've been working on it, but the error will still be apparent.
create table fkoo ( -- the FK table used to cause an error
afk int not null Primary key)
go
create table foo ( -- the table being inserted into
a int foreign key references fkoo(afk),
b varchar(128))
go
insert into fkoo (afk) -- valid FK values
values (1)
go
insert into fkoo (afk)
values (2)
go
insert into fkoo (afk)
values (3)
go
create procedure spfoo (@a int)
-- calling this sproc with anything other than 1, 2, or 3 will cause an FK
-- constraint violation
-- might be different than the original one listed. I was trying some stuff.
as
BEGIN TRANSACTION XXX
declare @localerr int
set @localerr = 0
insert into foo (a, b)
values (@a, 'spfoos')
set @localerr = @@ERROR
insert into foo (a, b)
values (1, 'this code should be executed!')
select 'post error code has been executed'
if @@ERROR = 0
begin
commit transaction XXX
return 0
end
else
begin
rollback transaction XXX
return 1
end
go
March 15, 2005 at 12:35 pm
Thanks for the extra info. I'll play with it in just a bit.
Have you tried running exactly the example from books on line? From the topic "SET XACT_ABORT"? What are the results?
Wayne
March 15, 2005 at 12:45 pm
I just ran the BOL example, and it worked like expected (rows 1 and 3 added). I made it a point to run it in our production database. Could have it have anything to do with naming the transactions? The fact that my code is in a stored procedure?
Thanks again for the help Wayne.
March 15, 2005 at 1:00 pm
Well, we have demonstrated that the database behaves as expected when presented with correct code. Since the database is not changing between test runs, that suggests that the problem lies within your code.
I see one logical error fairly quickly in the following code:
select 'post error code has been executed'
if @@ERROR = 0
begin
commit transaction XXX
return 0
end
else
begin
rollback transaction XXX
return 1
end
You are testing @@Error instead of @localerr. @@Error will always equal zero in this case as the select is unlikely to fail. The effect here is that you will always commit your transaction unless there is a catastrophic failure before getting to that line.
Also, from books on line: "It is required that XACT_ABORT be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server." Because you are working in an explicit transaction, I suspect that SQL Server is helping you out by turning XACT_ABORT back on without telling you.
hth
Wayne
March 15, 2005 at 1:11 pm
you're right on testing @@error. I should be testing @localerr. point taken.
The issue however, is the fact that NO CODE after the failing statement in spfoo gets executed, including the line immediately after.
If you debug this sproc in Query Analyzer and give @a an invalid value (> 3), you'll see that even the debugger kicks out on that insert line.
This is the crux of the biscuit. I didn't see that bit in BOL about xact_abort on explicit transactions. How do I get there? Why would that be the case? What's the point of having explicit transactions if the batch will always fail on error?
thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply