Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Occasionally in one of our production environments (we have a dozen or so without different clients pointing to each) we get exceptions at different points in the process. The code doesn't do anything fancy - just your basic inserts and updates. We are however, performing multiple inserts and updates within a single connection across multiple methods.

We'll get exceptions like this:

System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)....

and this:

System.Transactions.TransactionManagerCommunicationException: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. ---> System.Runtime.InteropServices.COMException (0x8004D024): The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)
at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize, Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier, OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim)
at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)
--- End of inner exception stack trace ---
at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)

and this:

System.InvalidOperationException: Invalid attempt to call Read when reader is closed.
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()

I don't think we need to turn on MSDTC (again things work in every other environment). Suggestions on what we can check code-wise or environment-wise?

share|improve this question
    
Why the votes to close?? I think this is a code issue, not a database engine one. –  Thomas Stringer Nov 9 '11 at 21:11
add comment

migrated from stackoverflow.com Nov 10 '11 at 8:54

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 0 down vote accepted

What do your methods look like that are calling the Database commands? It sounds like you have an open connection spanning across multiple blocks of logical code for a very long time.

I recommend calling SqlConnection.Open() right before you need the connection open, and SqlConnection.Close() when you are complete with the connection. This should all be taking place in a try/catch block.

If you are doing the above method, that means the connection isn't timing out. In which case, you need to look to the SQL Server error log to see if there are any errors being thrown that you need to look at.

share|improve this answer
    
thanks for the response. We are performing multiple methods with database updates in each while keeping open the connection/transaction. I assume the suggestion would be to go to MSDTC and I can understand the reason why, but it doesn't make sense why this is only occurring in one of our environments. –  Arthur Frankel Nov 9 '11 at 21:31
    
Why MSDTC?? I'd look directly towards the database for direct errors or something along those lines. The timeout for your instance in that one environment could be shorter, causing it to happen faster. Hard telling, but it looks like SQL Server is shutting down the connection before you want it to. –  Thomas Stringer Nov 9 '11 at 21:34
    
Yes, you're probably right. There's probably something in the environment that's different - we did check the timeout setting and that looked right. I'll check your answer since we'll probably find more info after we get the logs from production. –  Arthur Frankel Nov 9 '11 at 21:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.