SQL Server 2008 - problem with transaction

[pandatime]

Hi,

This is what I am doing. Basically I want all inserts and deletes to run, or not at all.

CREATE PROC myProc
AS
BEGIN

BEGIN TRAN
BEGIN TRY

insert to table x...
insert to table y...
insert to table z...
delete table a...

END TRY

BEGIN CATCH
  do something...
END CATCH

IF @@TRANCOUNT > 0
  COMMIT TRAN

END

This code seemed to work fine in 2005. I don't know if anything has changed in 2008, but I'm getting the following error:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

I really don't understand what the problem is!

Any ideas greatly appreciated!!!

Thank you

 

[markros]

I believe you need to open transaction in BEGIN TRY block.

Check this blog post for the sample:
How to insert information into multiple related tables

PluralSight Learning Library

 

[Olaf Doschke]

As previous count of transactions (before EXECUTEing this proc) is 0 and current count 1, you're leaving one transaction open.

Obviously not here. Here you're committing the transaction, if it wasn' already committed. That is a risc on it's own, but in case you'd put that somewhere you don't start a transaction it could only lead to premature commits, not to missing commits.

Is there any trigger for one of the inserts or deletes maybe beginning another transaction and not closing it?

So, is the error somewhere else within code you trigger?

Bye, Olaf.