Wednesday, February 15, 2012

Error 209 Ambiguous column name ...Transaction count after EXECUTE indicates that a COMMIT

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

It maybe the scope of your temp tables, try changing them to global temp tables with ## instead of one #. And you may need Transaction savepoints between transactions because I think this is SQL Server 2005 complaining about T-SQL technically none atomic Transactions. Hope this helps.|||It's because the stored procedure is aborted at the point you have the ambiguous column. It's not continuing your code and executing the rollback or the commit, hence you now have an uncommited transaction.|||i'm using sql server 2000, and immediate after this i'm checking if @.@.error <> 0 then goto Error_Handler where i rollback transaction|||I see that. It still doesn't execute it. It stops immediately because it got a compilation error trying to resolve your query. Do not pass go, do not execute if @.@.error.|||

Yes Motley is right, not all error can be processed using IF @.@.ERROR<>0. Some errors will cause break the whole batch so that the error handling is skipped. This shows the flaw in SQL2000 error handling. SQL2005 has much enhancement in error handling, which allows you to use TRY..CATCH block. You can take a look at this article:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp?

No comments:

Post a Comment