Snapshot Isolation Level & MSDTC

• Martin Gleeson

  Ten Centuries

  Points: 1096

  More actions

  October 18, 2007 at 6:08 am

  #180465

  Hi all,

  I have a general question about the snapshot isolation level in SQL Server 2005 and the MS DTC.

  Let's say I set a database's transaction isolation level to snapshot:

  USE Master

  ALTER DATABASE MyDB

  SET ALLOW_SNAPSHOT_ISOLATION ON

  GO

  Now, say I have simple query along the lines of:

  SELECT * FROM MyTable

  But this query is, for arguments sake, being passed in by the MSDTC.

  Now, according to the BOL article for "Choosing Row Versioning-based Isolation Levels", snapshot isolation does not support distributed queries.

  Does this mean that even our simple query, passed through MSDTC will be considered a distributed query and so be rejected?

  http://msdn2.microsoft.com/en-us/library/ms189122.aspx

  http://msdn2.microsoft.com/en-us/library/ms130975.aspx

  http://msdn2.microsoft.com/en-us/library/tcbchxcb(vs.80).aspx

  http://technet.microsoft.com/en-gb/library/ms345124.aspx

  http://msdn2.microsoft.com/en-us/library/ms188277.aspx

  http://msdn2.microsoft.com/en-us/library/ms188721.aspx

  http://msdn2.microsoft.com/en-us/library/ms130800.aspx

  http://support.microsoft.com/kb/899191/[/url]

  Ya know, just a quick question. :hehe:

• eejunk

  SSC Journeyman

  Points: 93

  More actions

  October 23, 2013 at 2:00 am

  #1660810

  Hi there,

  did you ever get an answer to that question?? we too are using MSDTC, but on a single DB (so it's not really distributed), but the MSDTC can wrap all of our .net calls into one transaction. anyways, we wanna upgrade to the Snapshot isolation but appears that the MSDTC (at least with EnterpriseServices) does not support it...

  If you got passed your problem, I would love to hear how. thanks!!