SQL Packager not keeping the READ_COMMITTED_SNAPSHOT SQL option ON

Category: Troubleshooting & error messages
Date: 08 Jun 2009
Product: SQL Packager
SQL Packager not keeping the READ_COMMITTED_SNAPSHOT SQL option ON

Unfortunately it is not possible to configure SQL Packager to create a database setting READ_COMMITTED_SNAPSHOT. When you package a database for a new install, the transaction isolation level will always be unspecified, which will more than likely set it to "read committed".

There are a couple of workarounds:

1. Use a post SQL Script:

If you run the package from the command line, you can specify /postsql:<filename>. You could use a sql file that contains the ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON, which will be run after the database is created.

2. Edit the SQL Package and add the code to set the user options.

To do this, output the package as a C# project, then in the PackageExecutor class add the following code after the dboptions were set:

sqlCommand.CommandText = "Alter database " + m_DatabaseName + " SET ALLOW_SNAPSHOT_ISOLATION ON";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Alter database " + m_DatabaseName + " SET READ_COMMITTED_SNAPSHOT ON";
sqlCommand.ExecuteNonQuery();

This sets the isolation level to 'read committed snapshot' confirmed by checking DBCC USEROPTIONS after the database is installed.