We have recently upgraded our server from SQL Server 2005 to SQL Server 2008. Previously we were running our asp.net application using MSDTC with no problems.
Since we upgraded, we are encountering problems that seem to relate to Multiple Active Result Sets (MARS). The error we receive is: "There is already an open DataReader associated with this Command which must be closed first"
when we try and perform a save process. To fix this problem we added MultipleActiveResultSets=True to the connection string. We now get the error:
"The transaction operation cannot be performed because there are pending requests working on this transaction."
Within the transaction scope we save to a table and then load from the same table. Our stack trace indicates this is where the first error occurs. Again, this transaction worked using SQL Server 2005.
If we do not use MSDTC by putting Enlist=false in the connection string, the request works.
We are really stumped as to what to do. We would prefer not to have to role back to SQL Server 2005 and would also prefer not to have to have sweeping code changes in our application. We have test MSDTC by using DTC ping on our servers and it
Your help is greatly needed and would be greatly appreciated.
Steve and Nilesh
View Complete Post