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
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