I am testing an upgrade from SQL Server 2005 to SQL Server 2008 R2. I have a stored procedure which attempts to use the serializable transaction isolation level on a distributed transaction (linked server).
The query fails on SQL Server 2008 R2 with
OLE DB provider "SQLNCLI10" for linked server "SERVER1" returned message "No transaction is active.".
Msg 7391, Level 16, State 2
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVER1" was unable to begin a distributed transaction.
If I change the isolation level to read committed, the transaction succeeds. This same procedure worked on SQL Server 2005 but now fails on SQL Server 2008 R2 using the same isolation level.
Is there a way to get this isolation level to work with linked servers on SQL Server 2008 R2?
View Complete Post