Thursday, May 20, 2010 3:25 PM
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.
Sunday, May 30, 2010 9:32 AMAnswererRemote access is not supported for transaction isolation level "SNAPSHOT".
Wednesday, May 02, 2012 1:24 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.
My blog: http://aboutsqlserver.com
- Marked As Answer by nadirsql Friday, June 15, 2012 1:56 AM