Vault SQL Database Options

[ian_sg]

In Vault 3.0.2 and up the database uses computed columns, which requires the following settings:

Code: Select all

Setting                   Default  sgvault
------------------------- -------- -------
ANSI_NULLS                Off      On
ANSI_PADDING              Off      On
ANSI_WARNINGS             Off      On
ARITHABORT                Off      On
CONCAT_NULL_YIELDS_NULL   Off      On
QUOTED_IDENTIFIER         Off      On
NUMERIC_ROUNDABORT        Off      Off

The first column is the default for a new database, the second column is the required setting for the sgvault database.

Ordinarily these options are set when you install or upgrade Vault and you never have to worry about them. There are two situations where these settings typically come up and cause confusion, however:

1) SQL Server maintenance plans

The jobs created by SQL Server's maintenance plan wizards don't work with QUOTED_IDENTIFIER turned on. To perform maintenance on your sgvault database (which is absolutely a good idea), we've provided scripts that you can schedule here: http://support.sourcegear.com/viewtopic.php?t=2924

As an alternative, if you're running SQL 2000 SP4, there is an additional flag you can include in your wizard-generated maintenance jobs to work around the issue, as described here: http://support.microsoft.com/kb/902388/

2) Errors related to ARITHABORT in the Vault server log

Under some circumstances when an sgvault database is backed up, moved to a new server, and restored, ARITHABORT is set incorrectly on the restore. Users typically notice this right away because you can no longer commit certain changes. The errors in the server log (%WINDIR%\Temp\sgvault\sgvault.log) typically look something like this:

System.Data.SqlClient.SqlException: UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'

To see the current setting for ARITHABORT (and related options) you can run this script:

Code: Select all

SELECT
	DATABASEPROPERTYEX('sgvault', 'IsAnsiNullsEnabled') AS [ANSI_NULLS],
	DATABASEPROPERTYEX('sgvault', 'IsAnsiPaddingEnabled') AS [ANSI_PADDING],
	DATABASEPROPERTYEX('sgvault', 'IsAnsiWarningsEnabled') AS [ANSI_WARNINGS],
	DATABASEPROPERTYEX('sgvault', 'IsArithmeticAbortEnabled') AS[ARITHABORT],
	DATABASEPROPERTYEX('sgvault', 'IsNullConcat') AS [CONCAT_NULL_YIELDS_NULL],
	DATABASEPROPERTYEX('sgvault', 'IsQuotedIdentifiersEnabled') AS [QUOTED_IDENTIFIER],
	DATABASEPROPERTYEX('sgvault', 'IsNumericRoundAbortEnabled') AS [NUMERIC_ROUNDABORT]

Your results should look like this:

Code: Select all

ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER NUMERIC_ROUNDABORT
---------- ------------ ------------- ---------- ----------------------- ----------------- ------------------
1          1            1             1          1                       1                 0                

1 means "on" and 0 means "off".

If your results don't match these, the following script will set all of these options correctly:

Code: Select all

EXEC sp_dboption @dbname = 'sgvault', @optname = 'ANSI nulls', @optvalue = 'on'
EXEC sp_dboption @dbname = 'sgvault', @optname = 'ANSI padding', @optvalue = 'on'
EXEC sp_dboption @dbname = 'sgvault', @optname = 'ANSI warnings', @optvalue = 'on'
EXEC sp_dboption @dbname = 'sgvault', @optname = 'arithabort', @optvalue = 'on'
EXEC sp_dboption @dbname = 'sgvault', @optname = 'concat null yields null', @optvalue = 'on'
EXEC sp_dboption @dbname = 'sgvault', @optname = 'quoted identifier', @optvalue = 'on'
EXEC sp_dboption @dbname = 'sgvault', @optname = 'numeric roundabort', @optvalue = 'off'