January 20, 2017 at 1:34 pm
Just had an odd case where we usually have a stored proc called by a non-transactional gui, but now have it being called by another stored proc that is creating a transaction before calling our code. The problem is occurring when there's an error, it's setting an error which we can';t seem to trap.
I've created a small piece of code to demonstrate the problem..
BEGIN TRANSACTION ExternalProc
SET NOCOUNT ON;
SET XACT_ABORT ON;
declare @work table ( id integer primary key )
declare @L integer
set @L = 0
insert into @Work (id) values (2) -- create primary key to cause conflict
WHILE @L < 2 BEGIN
SET @l = @l + 1
print '------ Loop #' + convert(varchar,@L) + ' ------'
BEGIN TRY
print 'In TRY'
BEGIN TRANSACTION InnerProc
print ' Inserting ' + convert(varchar,@L)
insert into @Work (id) values (@L)
COMMIT TRANSACTION InnerProc
print 'End TRY'
END TRY
BEGIN CATCH
print 'In CATCH'
print ' ' + ERROR_MESSAGE()
IF @@Trancount > 0 ROLLBACK TRANSACTION InnerProc
print 'End CATCH'
END CATCH
END
select * from @Work
COMMIT TRANSACTION ExternalProc
It gets an error on the rollback in the CATCH block, and if we take that out, it gets an error on the ExternalProc COMMIT.
We have no control over what calls our code, so it may or may not be in a transaction; is there anything we can do about this ?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply