April 19, 2011 at 5:48 am
I am using Sql Server 2005.
I have created one SP and this SP contains series of select,insert etc statements.My requirement is : If all the statement inside try clause execute successfully then commit the transaction else roll back all transaction.
But after execution i am getting the below error:
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. ---> System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
My SP is like this..please let me know if any changes require in below script.
CREATE Procedure [dbo].[InsertDataIntALL]
(
@ToValueDate bigint,
@FromValueDate bigint
)
AS
SET NOCOUNT on
BEGIN TRY
BEGIN TRANSACTION
SELECT A.* INTO #A FROM A with (nolock)
WHERE [ValueDate] = @FromValueDate
SELECT B.* INTO #B FROM B with (nolock)
WHERE [ValueDate] = @FromValueDate
SELECT C.* INTO #C FROM C with (nolock)
WHERE [ValueDate] = @FromValueDate
Delete from A where ValueDate = @ToValueDate
Delete from B where ValueDate = @ToValueDate
Delete from C where ValueDate = @ToValueDate
UPDATE A SET [ValueDate] = @ToValueDate
UPDATE B SET [ValueDate] = @ToValueDate
UPDATE C SET [ValueDate] = @ToValueDate
INSERT INTO A SELECT * FROM #A
INSERT INTO B SELECT * FROM #B
INSERT INTO C SELECT * FROM #C
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 --some error has occurred
ROLLBACK TRAN --so rollback all transactions
END CATCH
April 19, 2011 at 7:53 am
How is the stored procedure being called? Is there another outer transaction?
Have you run DBCC OPENTRAN() to see what the open transaction is?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 27, 2011 at 3:09 pm
Seems to me that you had un-closed transaction in your session before you even called this procedure.
Exception occured and rolled-back all. Try without "try-catch" to see which exception occured.
Also, check @@TRANCOUNT right at the beggining of the procedure to see if you are and how deep in a transaction.
How SQL Server actually works with transactions:
Only first BEGIN TRAN actually starts a transaction. All subsequent (nested) BEGIN TRANS just increase @@TRANCOUNT and do nothing else. Subsequent COMMIT TRAN just decrease @@TRANCOUNT and do nothing else, until the outermost COMMIT TRAN (the one firing when @@TRANCOUNT = 1) which actually commits the whole (outermost) transaction. However, if you had a ROLLBACK TRAN executed anywhere in the code, no matted how deep it was nested it immediately rollbacks the outermost transaction, and you are immediately out of the transaction (@@TRANCOUNT = 0).
If you call COMMIT TRAN or ROLLBACK TRAN when @@TRANCOUNT=0, you will get the error.
Because of that, it is recommended to ALWAYS put "IF @@TRANCOUNT > 0" before COMMIT and ROLLBACK commands.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply