Error 209 Ambiguous column ..Transaction count after EXECUTE indicates .....

i'm getting following exception when i try to execute stored procedure.

{"Ambiguous column name 'MemberID'.\r\nTransaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }

I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting
"Transaction count after EXECUTE indicates that......"

In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN

/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */

here is my stored proc

CREATE PROCEDURE dbo.ExportFile
@.intMonth INT,
@.intYear INT,
@.dtFirstDayOfMonth DATETIME

AS

BEGIN

BEGIN TRANSACTION

/*
I have some logic here that will select rows into temporary table
#TEMPRESULT
*/
UPDATE
dbo.RebateInstanceItem
SET
ResubmitCreated = @.dtmNewCreated
FROM #TEMPRESULT tTempResult
WHERE
MemberID = tTempResult.MemberID
AND RebateInstanceItem.IsResubmit = 'Y'
AND (RebateInstanceItem.ResubmitCreated = @.dtmLastCreated
OR RebateInstanceItem.ResubmitCreated IS NULL)

IF @.@.ERROR<>0
GOTO ERR_HANDLER // when error it will goto error_handler that will rollback

DROP TABLE #TEMPRESULT
IF @.@.ERROR<>0
GOTO ERR_HANDLER

COMMIT TRANSACTION
RETURN 0
END

ERR_HANDLER:
ROLLBACK TRANSACTION
RETURN 1
GO

The procedure stops immediately after an error like this. In fact, 2005's try..catch will not even stop it. It is pretty rare, and I am guessing that you modified one of the objects after creating the procedure:

create table testCrash
(
testCrashId int,
value varchar(10)
)
create table testCrash2
(
testCrash2Id int
)
go
create procedure testCrash$test
as
begin tran

select value
from testCrash
cross join testCrash2

rollback tran
go
exec testCrash$test
go
alter table testCrash2
add value varchar(10)
go
exec testCrash$test
go
Msg 209, Level 16, State 1, Procedure testCrash$test, Line 4
Ambiguous column name 'value'.
Msg 266, Level 16, State 2, Procedure testCrash$test, Line 4
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
go

|||

some errors are transaction aborting and can't be

handled by any sql server error handling mechanism

|||

This doesn't abort the transaction. That is what the error message is stating :) Run the batch and check the @.@.trancount. It will be 1, not 0.

It aborts the procedure only (even with try...catch in 2005, it busts out of the try...catch too!)

|||i'm using SQL SERVER 2000. and if you see message it says Transaction count 1, in .net quick watch error number 209. but immediatly after that if you goto query analyzer and try to rollback transaction it says NO transaction found.|||

How do you verify if there is an open transaction when you come into QA?

Do you:
select @.@.trancount
or
dbcc opentran(myDbName)

/Kenneth

|||

You cannot rollback a transaction in a different session. You will have to kill the connection or do the rollback on the connection where the error occurred. If you run the code I gave you in QA it will be in a transaction.

The best cure for this issue is to be careful with your source control. This really should never happen in a production system, and should be corrected immediately if you happen to find the issue in development.

|||if there is any error (which can stop the execution of T-SQL) within in a transaction then the transaction will be rolled back automaticall before the execution is going to abort.