Changing the isolation level of a transaction to SNAPSHOT will cause an exception.

Repro:

-- step 1 start

create databaseSnapshotPromotion

go

alter databaseSnapshotPromotion

setallow_snapshot_isolationon

go

alter databaseSnapshotPromotion

setread_committed_snapshoton

go

useSnapshotPromotion

go

create tabledbo.test

(

cint

)

go

-- step 1 end

-- step 2 start

settransaction isolation level read committed

begin tran

print'Read committed using snapshots'

print@@trancount

insertdbo.test

(

c

)

select1 as c

go

-- step 2 end

-- step 3 start

settransaction isolation level snapshot

print'Snapshot'

print@@trancount

insertdbo.test

(

c

)

select2 as c

commit tran

print@@trancount

go

select*

fromdbo.test

-- step 3 end

-- step 4 start

usemaster

go

settransaction isolation level read committed

go

drop databaseSnapshotPromotion

go

-- step 4 end

Changing the isolation level from SNAPSHOT is supported.

E.g.:

-- step 1 start

create databaseSnapshotPromotion

go

alter databaseSnapshotPromotion

setallow_snapshot_isolationon

go

alter databaseSnapshotPromotion

setread_committed_snapshoton

go

useSnapshotPromotion

go

create tabledbo.test

(

cint

)

go

-- step 1 end

-- step 2 start

settransaction isolation level snapshot

begin tran

print'Snapshot'

print@@trancount

insertdbo.test

(

c

)

select1 as c

go

-- step 2 end

-- step 3 start

settransaction isolation level read committed

print'Read committed using snapshots'

print@@trancount

insertdbo.test

(

c

)

select2 as c

commit tran

print@@trancount

go

select*

fromdbo.test

-- step 3 end

-- step 4 start

usemaster

go

settransaction isolation level read committed

go

drop databaseSnapshotPromotion

go

-- step 4 end