|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
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 ) Sql code: Code: CREATE PROCEDURE [dbo]. [MyProcedure] ( - List of parameters ) AS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION MyTransaction - Treatment COMMIT TRANSACTION MyTransaction Quote:
Thank you in advance, |
#2
| |||
| |||
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:
|
#3
| |||
| |||
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!
__________________ Grand Theft Auto 4 PC Video Game |
#4
| |||
| |||
Re: TransactionScope and transaction in SQL Server 2005 Quote:
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 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. |
#5
| |||
| |||
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. |
#6
| |||
| |||
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. |
#7
| |||
| |||
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 |
#8
| |||
| |||
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? |
![]() |
|
Tags: 2005, sql, transaction, transactionscope |
Thread Tools | Search this Thread |
|
![]() | ||||
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 |