TransactionScope and transaction in SQL Server 2005

[Dadhij]

Hello,

I developed a WebMethod that uses a transaction:

C # code:

 
using (TransactionScope scope = new TransactionScope ()) 
  ( 
	  SqlConnection connection = null; 
	  SqlCommand command = null; 

	  [..] / / Treatment 
	
	  try 
	  ( 
		  / / Calling a stored procedure using a transaction 
		  command. ExecuteNonQuery (); 
	  ) 
	  catch (Exception ex) 
	  ( 
		  log. Error (eg Message); 		
		  throw new Exception ( "Error in the backup database.", ex); 
	  ) 
	
	  trs. Complete (); 
	
	  [..] / / Treatment 
  )

As you can see this WebMethod uses a stored procedure that uses a transaction. This stored procedure developed in a database in SQL Server 2005 is outlined below:


Sql code:

  CREATE PROCEDURE [dbo]. [MyProcedure] 
  ( 
	  - List of parameters 
  ) 
  AS 

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
  BEGIN TRANSACTION MyTransaction 
	
	  - Treatment 

COMMIT TRANSACTION MyTransaction

Sometimes the exception with the following message is launched by the database:

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

What do I do to fix this problem?
Thank you in advance,

[JoeFrat]

The management of your transaction is done twice: in. Net and in the stored procedure. If you want to manage your next transaction. Net removes your

BEGIN TRANSACTION MyTransaction
COMMIT TRANSACTION MyTransaction

[Reegan]

My friends, we should never drive the client-side transactions.We should not manage the transaction in a stored procedure if you are already on the client side. Take your choice!

[Dadhij]

Originally Posted by Reegan

My friends, we should never drive the client-side transactions.We should not manage the transaction in a stored procedure if you are already on the client side. Take your choice!

For what reason?

I lacked precision in the description of my code. I forgot a backup database, which is why I used a transaction in this method.
Here are more accurately WebMethod that I developed:

C# code:

 using (TransactionScope scope = new TransactionScope ()) 
  ( 
	  SqlConnection connection = null; 
	  SqlCommand command = null; 

	  [..] / / Treatment 
	        
	  try 
	  ( 
		  / / Calling a stored procedure 1st safeguarding data in the database. 
                  / / This procedure does not use a transaction 
		  command. ExecuteNonQuery (); 
	  ) 
	  catch (Exception ex) 
	  ( 
		  log. Error (eg Message); 		
		  throw new Exception ( "Error in the backup database.", ex); 
	  ) 

	  [..] / / Treatment 
	
	  try 
	  ( 
		  / / Calling a stored procedure 2nd saving data in database 
                  / / This procedure uses a transaction 
		  command. ExecuteNonQuery (); 
	  ) 
	  catch (Exception ex) 
	  ( 
		  log. Error (eg Message); 		
		  throw new Exception ( "Error in the backup database.", ex); 
	  ) 
	
	  trs. Complete (); 
	
	  [..] / / Treatment 
  )

I used a transaction in this case WebMethod for that failure can return to the original state at any time. For example, if the backup data in a database fails when calling the second procedure, the data before calling the procedure must first be restored in the database.

I then used a transaction in the second procedure in case of failure of treatment performed (changing tables), the initial state is restored.

Thank you in advance for your help.

[Sloane]

If you use the transactionScope does not complete transaction in SP even if you use multiple tables. The changes are only 'committee' that when you will commit () on the scope.

[Dadhij]

Hello,

following your comments, I deleted the transaction in the stored procedure. Despite this I still got the same error message. I would like to get back to you because the problem persists. As I said, I deleted the transaction in the stored procedure following the comments that you made me.

Thank you in advance for your help.

[Zecho]

What happens if your client stops before confirming the transaction? The answer is probably a crash and processes awaiting the release of resources locked by your transactionscope.

This is the transactionscope to be eliminated rather than delete the transaction in the SP.

Code:

  CREATE PROCEDURE PROCGENERAL 
  AS 
  BEGIN TRANSACTION 
         EXEC PROC1 
         EXEC PROC2 
 COMMIT

Of course it is just a model. You only have to play the SP client code from a C #

[Dadhij]

I advise you not to use TransactionScope but a stored procedure. The disadvantage of a stored procedure is that it is not possible to my knowledge to pass parameters of any type of data such as tables of value or more complex types. I use SQL Server 2005. Do you think I should use a managed stored procedure?