"The operation is not valid for the state of the transaction" error and transaction scope
Solution 1
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
}
}
Solution 2
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:
<system.transactions>
<machineSettings maxTimeout="00:05:00" />
</system.transactions>
Solution 3
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();
...
}
Solution 4
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?
Solution 5
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.
Related videos on Youtube
Michael Kniskern
I am currently working as an IT engineer for the government of Mesa, Arizona USA
Updated on July 05, 2022Comments
-
Michael Kniskern almost 2 years
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?
-
Michael Kniskern over 15 yearsI do not have any t-sql transaction code in any of my stored procedures. In theory, the transaction should be controlled by the MyAddUpdateMethod()
-
rageit about 12 yearsI stumbled onto same situation. I had to refer to two different databases within the same transaction scope. Thanks for the tip.
-
D_Bye almost 12 yearsGood catch, a common place to see this happen is if you have a logging framework (nlog, log4net) that is writing to the DB, since the logging framework will create its own connection to the database as your application.
-
Dr. Ogden Wernstrom about 9 yearsI strongly suspect that it's not the timeout that changed the behaviour but the fact that you've changed the isolation level from Serializable to ReadCommitted.
-
user2864740 over 7 yearsLogging applications should probably suppress any outer TransactionScope context.
-
codeMonkey about 7 years+1 because I have received this error when the transaction language in my stored proc was wrong, i.e. transaction counts being messed up.
-
Kiquenet over 5 yearsIs the same than
TransactionOptions.Timeout
? -
Alex over 4 yearsNLog supresses any outer TransactionScope: github.com/NLog/NLog/wiki/Database-target
-
DdW almost 4 yearsNote that there's a machinewide .NET framework config that overrules this when it's over 10 minutes!
-
Rajaram Shelar over 3 yearsI opened same connection twice with using statements under same TransactionScope but still worked. I used SqlConnection instead of SQLServer as used in your example above.
-
Pawel Maga about 3 yearsThis exception can also occur in the case of timeout and the information about timeout should be also included in the exception data
-
lilo.jacob over 2 years@KJN thank you! Should we do this on the machine where SQL Server works?
-
J. Minjire over 2 yearsYes, do it where sql server is running from.