Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 15-01-2009
Member
 
Join Date: Nov 2008
Posts: 31
TransactionScope and transaction in SQL Server 2005

Hello,

I developed a WebMethod that uses a transaction:

C # code:
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:
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:

Quote:
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,
Reply With Quote
  #2  
Old 15-01-2009
Member
 
Join Date: May 2008
Posts: 115
Re: TransactionScope and transaction in SQL Server 2005

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

Quote:
BEGIN TRANSACTION MyTransaction
COMMIT TRANSACTION MyTransaction
Reply With Quote
  #3  
Old 15-01-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: TransactionScope and transaction in SQL Server 2005

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!
Reply With Quote
  #4  
Old 15-01-2009
Member
 
Join Date: Nov 2008
Posts: 31
Re: TransactionScope and transaction in SQL Server 2005

Quote:
Originally Posted by Reegan View Post
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:
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.
Reply With Quote
  #5  
Old 15-01-2009
Member
 
Join Date: Oct 2008
Posts: 88
Re: TransactionScope and transaction in SQL Server 2005

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.
Reply With Quote
  #6  
Old 15-01-2009
Member
 
Join Date: Nov 2008
Posts: 31
Re: TransactionScope and transaction in SQL Server 2005

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.
Reply With Quote
  #7  
Old 15-01-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: TransactionScope and transaction in SQL Server 2005

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:
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 #
Reply With Quote
  #8  
Old 15-01-2009
Member
 
Join Date: Nov 2008
Posts: 31
Re: TransactionScope and transaction in SQL Server 2005

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?
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "TransactionScope and transaction in SQL Server 2005"
Thread Thread Starter Forum Replies Last Post
error initializing or updating the transaction in stream server MACALL Technology & Internet 5 04-07-2011 12:28 PM
SQL Server 2008 vs SQL Server 2005 technika Windows Software 5 30-12-2009 12:50 PM
SQL Server 2005 and IIS 7 Ximen Software Development 2 01-06-2009 01:54 PM
Upgrade fails: SQL Server Express 2005 to SQL Server 2005 Steve BB Small Business Server 3 11-06-2008 07:37 PM
Installing SQL Report Server 2005 on Server 2008 Marcos Benites Windows Server Help 1 25-04-2008 05:54 PM


All times are GMT +5.5. The time now is 08:56 AM.