locked
snapshot isolation level issue RRS feed

  • Question

  • when i run the following query :

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT
    begin transaction 
    select COUNT(*) from SERVERNAME.DATABASENAME.SCHEMANAME.TABLENAME
    commit transaction

     I get the error:

    Remote access is not supported for transaction isolation level "SNAPSHOT".
    

    Why is it not possible to have the snapshot isolation level if we want to access data from remote server? They have been set up as linked server.

    Thursday, May 20, 2010 3:25 PM

Answers

  • Snapshot isolation level guarantees consistency on transaction level. E.g. all data "kind of frozen" at the moment of begin transaction. (It's a bit more complicated but let's skip it for simplicity sake). On local server level it forced based on the version store in tempdb. Every time when you update the data in the database, "old" version of the row copied there. So when you try to read the data from within snapshot transaction, SQL Server would read corresponding version of the row from the version store.

    Obviously SQL Server does not have control how to enforce the same data consistency and rules on the linked server (which could be Oracle, CSV file, etc). So using linked servers violates data consistency principle and not supported.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by nadirsql Friday, June 15, 2012 1:56 AM
    Wednesday, May 2, 2012 1:24 AM

All replies

  • Remote access is not supported for transaction isolation level "SNAPSHOT".
    Sunday, May 30, 2010 9:32 AM
  • Snapshot isolation level guarantees consistency on transaction level. E.g. all data "kind of frozen" at the moment of begin transaction. (It's a bit more complicated but let's skip it for simplicity sake). On local server level it forced based on the version store in tempdb. Every time when you update the data in the database, "old" version of the row copied there. So when you try to read the data from within snapshot transaction, SQL Server would read corresponding version of the row from the version store.

    Obviously SQL Server does not have control how to enforce the same data consistency and rules on the linked server (which could be Oracle, CSV file, etc). So using linked servers violates data consistency principle and not supported.


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by nadirsql Friday, June 15, 2012 1:56 AM
    Wednesday, May 2, 2012 1:24 AM