Enabling Snapshot Isolation in SQL Server 2005
Enabling Snapshot Isolation is a two-step process. First, the DBA has to enable it per database. Then the queries have to execute in a session enabled for Snapshot Isolation.
To enable Snapshot Isolation on a database, use this:
ALTER DATABASE pubs SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE pubs SET READ_COMMITTED_SNAPSHOT ON
Here's what to type to enable the Snapshot Isolation in a session (this is needed only if you use the ALLOW_SNAPSHOT_ISOLATION option rather than READ_COMMITTED_SNAPSHOT):
SET TRANSACTION ISOLATION LEVEL SNAPSHOT