SQLServer Maintenance Plan Error

jmercker · Post by **jmercker** » Wed Feb 02, 2005 1:03 pm

This problem was forwarded to me my client's DBA. FYI: We did a default installation of Dragnet and then added the database to an existing maintenance plan that we had to manage Vault.

Also, we're running Dragnet for our consulting group and getting the same error so maybe is an installer issue (e.g. something introduced when the database is installed into SQLServer)?

Here's the information she forwarded...

=======
ERROR - [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

And when you look at the setting for QUOTED_IDENTIFIER (found by running DATABASEPROPERTYEX(sgdragnet, IsQuotedIdentifiersEnabled)) you receive a NULL value, which is an invalid input (should be 1 or 0).

I then tried to set the QUOTED_IDENTIFIER option, but running the procedure SET QUOTED_IDENTIFIER OFF, but it did not change the value. I was able to change the QUOTED_IDENTIFIER value on other databases on this instance of SQL Server.
=========

Thanks,
Jeramie

lbauer · Post by **lbauer** » Wed Feb 02, 2005 1:36 pm

[edited]

Dragnet requires the setting QUOTED_IDENTIFIER to be on, as Dragnet uses computed columns.

From SQL Books Online :

SET QUOTED_IDENTIFIER must be ON when creating or manipulating indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

However, the SQL Maintenance plan requires that it be OFF. I believe this is a bug which Microsoft plans to fix at some point.

lbauer · Post by **lbauer** » Wed Feb 02, 2005 1:49 pm

Sorry, the code I posted is for Vault, but the issue with QUOTED_IDENTIFIER is the same for Dragnet. I'll repost code when I get it modified.

jmercker · Post by **jmercker** » Thu Feb 03, 2005 9:01 am

Thanks for the feedback. I believe, from what our DBA was saying, that the option does not appear to be on for the Dragnet database installation (see her comments above about trying to turn it on...).

Post by **jclausius** » Thu Feb 03, 2005 9:17 am

Jeramie:

When the installation routine creates the Dragnet database, it runs the following command:

ALTER DATABASE sgdragnet SET QUOTED_IDENTIFIER ON
GO

It is extremely important this is ON during Dragnet operations.

If your DBA insists with running the maintenance plan, rather than using the corresponding DBCC commands, I believe job will succeed if QUOTED_IDENTIFIER has been temporarily switched OFF. However, during this time, please ensure no one accesses the Dragnet database. Once the job has completed, make sure QUOTED_IDENTIFIER has been turned back on.

Note, there may be some other options which may need to be temporarily reassigned as well ( i.e. ARITHABORT )

jmercker · Post by **jmercker** » Thu Feb 03, 2005 11:50 am

I don't think that she has set it on or off on her own. She came to me reporting that the maintenance plan reported a failure for the dragnet database. When she manually tried to check the status of that option on the database, null was returned for the value rather than 0 or 1.

Is there something that the maintenance plan would be doing to tinker with the value? My consulting company's installation has exactly the same issue as what this client's DBA was reporting.

FYI: The Vault database on the same server, in the same maintenance plan, does not report this problem. Only the dragnet database has this issue.

Thanks in advance + I appreciate the responses given so far...

Jeramie

Post by **jclausius** » Thu Feb 03, 2005 1:30 pm

jmercker wrote:Is there something that the maintenance plan would be doing to tinker with the value? My consulting company's installation has exactly the same issue as what this client's DBA was reporting.

AFAIK this must have been by an outside source. I know Dragnet will not change the value, and I think the SQL Maintenance plan leaves this value alone as well.

jmercker wrote:When she manually tried to check the status of that option on the database, null was returned for the value rather than 0 or 1.

The NULL value is strange. What is the result of sp_dboption 'sgdragnet', 'quoted identifier'? The value should be ON or OFF.

jmercker wrote:FYI: The Vault database on the same server, in the same maintenance plan, does not report this problem. Only the dragnet database has this issue.

Is it safe to assume you are using a Vault 3.0.1 or LOWER? Vault 3.0.2 contains computed columns, so the same restrictions regarding QUOTED_IDENTIFIER, ARITABORT, CONCAT_ANSI_NULLS, etc will apply.

jmercker · Post by **jmercker** » Thu Feb 03, 2005 2:33 pm

jclausius wrote: AFAIK this must have been by an outside source. I know Dragnet will not change the value, and I think the SQL Maintenance plan leaves this value alone as well.

Not sure on this one as two isolated installations have the same behavior and are administered by separate companies...

jclausius wrote:The NULL value is strange. What is the result of sp_dboption 'sgdragnet', 'quoted identifier'? The value should be ON or OFF.

When I run that against the database, I get ON.

jclausius wrote:Is it safe to assume you are using a Vault 3.0.1 or LOWER? Vault 3.0.2 contains computed columns, so the same restrictions regarding QUOTED_IDENTIFIER, ARITABORT, CONCAT_ANSI_NULLS, etc will apply.

We're running Vault 3.0.2 on that server upgraded from Vault 2.x.

Post by **jclausius** » Thu Feb 03, 2005 3:02 pm

As for Vault 3.0.2, unless it is temporarily being switched off, the maintenance plan should be failing.

If the maintenance plan is running against a Vault 3.0.2 database, then that is news to me. Even our own interal maintenance plans failed upon first installing Vault 3.0.2 due to the QUOTED_IDENTIFIER setting.

jmercker · Post by **jmercker** » Thu Feb 03, 2005 3:06 pm

[quote="jclausius"]As for Vault 3.0.2, unless it is temporarily being switched off, the maintenance plan should be failing.

If the maintenance plan is running against a Vault 3.0.2 database, then that is news to me. Even our own interal maintenance plans failed upon first installing Vault 3.0.2 due to the QUOTED_IDENTIFIER setting.[/quote]

On the status of the run, that is what is being reported by the DBA. How do you manage the rebuild of indexes, backup of transactions + data files + other maintenance tasks @ Sourcegear if the maintenance plan won't run?

Thanks in advance,
Jeramie

Post by **jclausius** » Thu Feb 03, 2005 3:22 pm

jmercker wrote:How do you manage the rebuild of indexes, backup of transactions + data files + other maintenance tasks @ Sourcegear if the maintenance plan won't run?

You can use a "custom based" T-SQL script based on the T-SQL sample found near the end of Maintenance: Vault/Dragnet database(s).

In case you are wondering, here is my own internal mapping of the maintenance plan options to T-SQL Code. Hopefully you can use this to create a maintenance job which suits your needs.

SQL Maint Reorganize data and index pages = T-SQL DBCC DBREINDEX
SQL Maint Update statistics = T-SQL UPDATE STATISTICS
SQL Maint Remove Unused Space = T-SQL DBCC SHRINKDATABASE
SQL Maint database integrity = T-SQL DBCC CHECKDB