The operation is not valid for the state of the transaction error and transaction scope

Question

I am getting the following error when I try to call a stored procedure that contains a SELECT Statement      The operation is not valid for the state of the transaction   Here is the structure of my calls   public void MyAddUpdateMethod          using  TransactionScope Scope   new TransactionScope TransactionScopeOption RequiresNew                 using SQLServer Sql   new SQLServer this m connstring                           do my first add update statement                do my call to the select statement sp             bool DoesRecordExist   this SelectStatementCall id                     public bool SelectStatementCall System Guid id        using SQLServer Sql   new SQLServer this m connstring     breaks on this line                 create parameters                      Is the problem with me creating another connection to the same database within the transaction

User · Answer

For any wanderer that comes across this in the future. If your application and database are on different machines and you are getting the above error especially when using TransactionScope, enable Network DTC access. Steps to do this are:

Add firewall rules to allow your machines to talk to each other.
Ensure the distributed transaction coordinator service is running
Enable network dtc access. Run dcomcnfg. Go to Component sevices > My Computer > Distributed Transaction Coordinator > Local DTC. Right click properties.
Enable network dtc access as shown.

Important: Do not edit/change the user account and password in the DTC Logon account field, leave it as is, you will end up re-installing windows if you do.

User · Answer

After doing some research  it seems I cannot have two connections opened to the same database with the TransactionScope block  I needed to modify my code to look like this   public void MyAddUpdateMethod         using  TransactionScope Scope   new TransactionScope TransactionScopeOption RequiresNew                 using SQLServer Sql   new SQLServer this m connstring                           do my first add update statement                                  removed the method call from the first sql server using statement         bool DoesRecordExist   this SelectStatementCall id           public bool SelectStatementCall System Guid id        using SQLServer Sql   new SQLServer this m connstring                   create parameters

User · Answer

I also come across same problem  I changed transaction timeout to 15 minutes and it works   I hope this helps   TransactionOptions options   new TransactionOptions    options IsolationLevel   System Transactions IsolationLevel ReadCommitted  options Timeout   new TimeSpan 0  15  0   using  TransactionScope scope   new TransactionScope TransactionScopeOption Required options         sp1        sp2

User · Answer

After doing some research  it seems I cannot have two connections opened to the same database with the TransactionScope block  I needed to modify my code to look like this   public void MyAddUpdateMethod         using  TransactionScope Scope   new TransactionScope TransactionScopeOption RequiresNew                 using SQLServer Sql   new SQLServer this m connstring                           do my first add update statement                                  removed the method call from the first sql server using statement         bool DoesRecordExist   this SelectStatementCall id           public bool SelectStatementCall System Guid id        using SQLServer Sql   new SQLServer this m connstring                   create parameters

User · Answer

For any wanderer that comes across this in the future. If your application and database are on different machines and you are getting the above error especially when using TransactionScope, enable Network DTC access. Steps to do this are:

Add firewall rules to allow your machines to talk to each other.
Ensure the distributed transaction coordinator service is running
Enable network dtc access. Run dcomcnfg. Go to Component sevices > My Computer > Distributed Transaction Coordinator > Local DTC. Right click properties.
Enable network dtc access as shown.

Important: Do not edit/change the user account and password in the DTC Logon account field, leave it as is, you will end up re-installing windows if you do.

User · Answer

I ve encountered this error when my Transaction is nested within another  Is it possible that the stored procedure declares its own transaction or that the calling function declares one

User · Answer

When I encountered this exception  there was an InnerException  Transaction Timeout   Since this was during a debug session  when I halted my code for some time inside the TransactionScope  I chose to ignore this issue   When this specific exception with a timeout appears in deployed code  I think that the following section in you  config file will help you out    lt system transactions gt            lt machineSettings maxTimeout  00 05 00    gt    lt  system transactions gt

User · Answer

When I encountered this exception  there was an InnerException  Transaction Timeout   Since this was during a debug session  when I halted my code for some time inside the TransactionScope  I chose to ignore this issue   When this specific exception with a timeout appears in deployed code  I think that the following section in you  config file will help you out    lt system transactions gt            lt machineSettings maxTimeout  00 05 00    gt    lt  system transactions gt

User · Answer

I ve encountered this error when my Transaction is nested within another  Is it possible that the stored procedure declares its own transaction or that the calling function declares one

User · Answer

I ve encountered this error when my Transaction is nested within another  Is it possible that the stored procedure declares its own transaction or that the calling function declares one

User · Answer

For me  this error came up when I was trying to rollback a transaction block after encountering an exception  inside another transaction block   All I had to do to fix it was to remove my inner transaction block   Things can get quite messy when using nested transactions  best to avoid this and just restructure your code

User · Answer

After doing some research  it seems I cannot have two connections opened to the same database with the TransactionScope block  I needed to modify my code to look like this   public void MyAddUpdateMethod         using  TransactionScope Scope   new TransactionScope TransactionScopeOption RequiresNew                 using SQLServer Sql   new SQLServer this m connstring                           do my first add update statement                                  removed the method call from the first sql server using statement         bool DoesRecordExist   this SelectStatementCall id           public bool SelectStatementCall System Guid id        using SQLServer Sql   new SQLServer this m connstring                   create parameters

User · Answer

I also come across same problem  I changed transaction timeout to 15 minutes and it works   I hope this helps   TransactionOptions options   new TransactionOptions    options IsolationLevel   System Transactions IsolationLevel ReadCommitted  options Timeout   new TimeSpan 0  15  0   using  TransactionScope scope   new TransactionScope TransactionScopeOption Required options         sp1        sp2

User · Answer

For me  this error came up when I was trying to rollback a transaction block after encountering an exception  inside another transaction block   All I had to do to fix it was to remove my inner transaction block   Things can get quite messy when using nested transactions  best to avoid this and just restructure your code

User · Answer

I ve encountered this error when my Transaction is nested within another  Is it possible that the stored procedure declares its own transaction or that the calling function declares one

User · Answer

After doing some research  it seems I cannot have two connections opened to the same database with the TransactionScope block  I needed to modify my code to look like this   public void MyAddUpdateMethod         using  TransactionScope Scope   new TransactionScope TransactionScopeOption RequiresNew                 using SQLServer Sql   new SQLServer this m connstring                           do my first add update statement                                  removed the method call from the first sql server using statement         bool DoesRecordExist   this SelectStatementCall id           public bool SelectStatementCall System Guid id        using SQLServer Sql   new SQLServer this m connstring                   create parameters

[c#] "The operation is not valid for the state of the transaction" error and transaction scope

Examples related to c#

Examples related to .net

Examples related to sql-server

Examples related to transactions

Examples related to transactionscope