DBCC failure against table tblfsobjectshares in DB sgvault

[christian]

We are running a Vault Server 3.1.1 installation.

When we attempt to run any of the suggested SourceGear maintenance scripts against the Vault database, the following error is invoked:

Table Name: tblfsobjectshares
Server: Msg 1934, Level 16, State 1, Line 1
DBCC failed because the following SET options have incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.

This also occurs when we attempt to run:

DBCC SHRINKDATABASE (sgvault, 10).

When we explicitly run the underlying DBCC commands against all of the sgvault tables apart from tblfsobjectshares, no error occurs.

The various properties returned by DATABASEPROPERTYEX(sgvault, <property>) are:

Collation = Latin1_General_CI_AS
IsAnsiNullDefault = 0
IsAnsiNullsEnabled = 1
IsAnsiPaddingEnabled = 1
IsAnsiWarningsEnabled = 1
IsArithmeticAbortEnabled = 1
IsAutoClose = 0
IsAutoCreateStatistics = 1
IsAutoShrink = 0
IsAutoUpdateStatistics = 1
IsCloseCursorsOnCommitEnabled= 0
IsFulltextEnabled = 0
IsInStandBy = 0
IsLocalCursorsDefault = 0
IsMergePublished = 0
IsNullConcat = 1
IsNumericRoundAbortEnabled = 0
IsQuotedIdentifiersEnabled = 1
IsRecursiveTriggersEnabled = 0
IsSubscribed = 0
IsTornPageDetectionEnabled = 1
Recovery = SIMPLE
SQLSortOrder = 0
Status = ONLINE
UserAccess = MULTI_USER
Updateability = READ_WRITE
Version = 539

Does anyone have any suggestions as to what the problem is?

[ian_sg]

Christian,

tblfsobjectshares has a computed column, so SQL Server requires that several options are the same when modifying data in the table as when the table was created.

If you add the following lines to the top of your maintenance scripts you should be all set:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

This will change these options for the current session only, so assuming you have these turned off intentionally for another database, this won't affect that.

[jclausius]

Your settings for the database look correct:

Options must be turned ON

• ANSI_NULLS,
• ANSI_PADDING,
• ANSI_WARNINGS,
• ARITHABORT,
• CONCAT_NULL_YIELDS_NULL,
• QUOTED_IDENTIFIER

Options must be turned OFF

• NUMERIC_ROUNDABORT

I wonder if your query tool is changing the settings from the database. Try running the following BEFORE your script executes:

Code: Select all

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
... Rest of script follows

[christian]

The script was being run as a stored procedure, which had been created in Enterprise Manager.

The problem persisted until the stored proc was dropped then recreated from SQL Query, preceded by the above suggested lines:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.usp_sgvault_RebuildIndices
<script>
GO

Incidentally, the stored proc was created in a separate DBA database, and not in sgvault.

Many thanks for your assistance - problem solved.