Quoted identifier change in 3.0.3

asills · Post by **asills** » Tue Mar 01, 2005 9:47 am

I upgraded my server from 3.0.1 to 3.0.3 last night and checked my backups still worked, however I didn't bother to check my nightly maintenance jobs and they're now failing becuase QUOTED_IDENTIFIER has been defaulted to off (and I'd much rather use the SQL2000 maintenance feature rather than doing the sql script myself).

When 3.0.1 was installed, QUOTED_IDENTIFIER was defaulted to on, but after 3.0.3 it was defaulted to off. Is it safe to switch it back on? Was it changed on purpose in the upgrade or did something weird happen?

Post by **jclausius** » Tue Mar 01, 2005 10:18 am

The Vault upgrade itself wouldn't turn it off, as it is still required for normal Vault operation.

Basically, these Database options must be set to ON - ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER. Also NUMERIC_ROUNDABORT must be set to OFF

Using different values will cause Vault to fail in certiain cases. So changing them is ill-advised.

Is it possible someone switched QUOTED_IDENTIFIER OFF so the maintenance plan could run? The SQL Server maintenance plans have a bug which require this to be set to OFF. That is why we've recommded the SQL script within the KB article.

asills · Post by **asills** » Tue Mar 01, 2005 10:26 am

No, they've been running successfully since I installed Vault 3.0.1 and setup the maintenance plans (I get emails every night when they run). It wasn't until the 3.0.3 upgrade did they start to fail.

There are only 2 people other than myself who would have access to this server and they work on separate databases, so any changes they might have made should only affect another database.

Post by **jclausius** » Tue Mar 01, 2005 10:38 am

That is strange. As a double check, I checked the upgrade script. Here is an excerpt:

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 = 'quoted identifier', @optvalue = 'on'

EXEC sp_dboption @dbname = 'sgvault', @optname = 'numeric roundabort', @optvalue = 'off'

When the installer runs the upgrade script, the options would have been configured as mentioned above.

asills · Post by **asills** » Tue Mar 01, 2005 10:44 am

Those options are all indeed set (I double-checked), but I assume those are the same options from 3.0.1 and my maintenance used to work but no longer works.

The main reason I don't want to use the SQL scripts provided in the KB article is it's more work for me to make sure everything succeeded. I get emails with log files from the maintenance jobs since that's build into xp_sqlmaint.

So what could have been different from the 3.0.1 install and the 3.0.3 install? The only thing that I did not do was restart the sql service after installing 3.0.3, but I don't see how that could have affected anything.

Post by **jclausius** » Tue Mar 01, 2005 10:51 am

I don't know why the maintenance plans would have succeeded, because those options do not require a SQL restart.

FWIW, here is a similar thread in Dragnet - SQLServer Maintenance Plan Error. Unfortunately for SQL Server 2000, you can run the script based plan.

I haven't messed with it, but if you could guarantee no one would be using Vault ( or you could temporarily bring down IIS / Vault server ), you could edit your maintenance plan script to turn QUOTED_IDENTIFIER OFF, run the maintenance, and set QUOTED_IDENTIFIER back on.

To me, this seems a little risky - what if the QUOTED_IDENTIFIER does not get turned back on?

In my opinion, it would be easier to adopt the SQL from the KB article, and schedule that to run in place of the SQL used to launch the SQL maintenance DLL.

asills · Post by **asills** » Tue Mar 01, 2005 11:04 am

Okay, I just installed Vault 3.0.1 on my machine and checked the quoted identifier value and it is indeed set to off after the Vault install.

Did post-3.0.1 change the Vault to on? Is it a bug in 3.0.1 that caused that value to not be set?

Post by **jclausius** » Tue Mar 01, 2005 11:12 am

Vault 3.0.2 and above will require the change. What is the value after installing Vault 3.0.3 or Vault 3.0.4?

BTW, are you "downgrading" a server? That could lead to other problems if the database has been "upgraded" to a newer version.

Post by **jclausius** » Tue Mar 01, 2005 11:17 am

Doh! It just hit me. The change was introduced in Vault 3.0.2, but you were sitting at Vault 3.0.1.

Sorry for being so dense.

That would explain the successful runs of the maintenance plan.

asills · Post by **asills** » Tue Mar 01, 2005 11:23 am

Vault 3.0.2 and above will require the change.

Okay that's what I was trying to get at (when it changed and/or why it changed), since I had developed a process/strategy based on the settings but had no knowledge into when upgrading to a new version why certain things that I technically relied upon had changed (I didn't see that information in either the 3.0.2 or 3.0.3 release notes).

I have no choice but to schedule the sql script provided in the KB article from this point forward as opposed to using the technically simpler (button clicks instead of modifying your sql script) maintenance jobs.

Before I go digging, do you know off the top of your head how I can duplicate what the maintenance plan does of emailing logs to an operator? I am still using the maintenance plan to perform a backup so I will still receive notification of it succeeding/failing, but I would really like the same now that I have to manually do some SQL for the reindex/defrag tasks. I might just have to do without a success email (since the job itself will fail and send a notice to an operator), although I don't like the idea as I inherently don't trust anything that contains my critical data

Post by **jclausius** » Tue Mar 01, 2005 11:37 am

Perhaps the easiest thing to do is :

1) Modify the SQL script so it checks the things you wish to check. To make things a bit nicer you may want to place the adopted SQL code within a stored procedure.

Also, you may want to check for (@@ERROR <> 0) within the script, and then do something like RAISERROR (N'Error during SQL Maintenance script.', 20, 1) when one of the DBCC commands returns an error.

2) Go to the jobs, and create a new job. On the Steps tab, create a new Step, and either use the T-SQL or call the stored procedure from step 1.

3) Schedule the job.

4) On the notifications tab, configure any notifications you wish to use.

5) Click OK to save the changes.

GregM · Post by **GregM** » Tue Mar 01, 2005 5:37 pm

Did I read this thread right? Installing Vault 3.0.2 is going to break my existing backup/maintenance plans? That's really not a good thing. What about our backups that are done using commercial products like Veritas? I'd really hate to "upgrade" the server, and suddenly find out that our nightly tape backups are failing, and thus useless.

Post by **jclausius** » Tue Mar 01, 2005 7:19 pm

Greg:

No, I don't think your backups are going to start failing.

Here's the summary:

- Vault 3.0.2 and above use a computed column within an index. For this computed column to work correctly, SQL Server 2000 requires the database to be configured in a certain manner. See http://msdn.microsoft.com/library/defau ... 5_8os3.asp for more information.

- Unfortunately, there seems to be a problem (bug) with the Maintenance plan for database optimizations created within SQL Enterprise Manager. The maintenance plan for db optimization uses the same DBCC commands as outlined in our SQL Maintenance article, but it sets its initial environment incorrectly. Unfortunately, the maintenance plan is an extended stored procedure which is hard coded, and cannot be corrected.

If you wanted to run the respective T-SQL code to do a maintenance check, the database options must be configured as specified in this KB article - http://support.microsoft.com/default.as ... -us;301292. This is why I consider the SQL Enterprise Manager behavior a bug. The Vault database is already configured in a manner which would allow DBCC commands to run correctly.

Now, this problem exists ONLY for Database optimization plans. The settings required for computed columns do not affect Enterprise Manager Backup Plans. Any backup plan created by the Backup/Maintenance within SQL Enterprise Manager still runs correctly.

As for Veritas, its been a long, long time since I've used their tools. Since SQL Enterprise Manager has no problems backing up a database, it would be hard to believe a computed column would cause a problem for any Veritas based backup.

HTH

GregM · Post by **GregM** » Tue Mar 01, 2005 8:03 pm

Jeff, I'm afraid I'm even more confused now. Those articles say it will fail if the setting isn't "ON", but I thought the 3.0.2 update just changed the setting from OFF to ON.

Post by **jclausius** » Tue Mar 01, 2005 9:12 pm

As the MS articles state, the DBCC commands require QUOTED_IDENTIFIER set to ON. If this setting is not configured correctly, some DBCC commands will fail.

Unfortunately, when SQL Server starts the maintenance plan it initializes the connection to have QUOTED_IDENTIFIER set to OFF. So when it uses this option set to off, some of the the DBCC commands fail when ran by the maintenance plan.
-------------------------------------
In other words, Vault requires the QUOTED_IDENTIFIER to be ON, the DBCC commands require QUOTED_IDENTIFIER to be ON, and the Maintenance Plan is hard-coded to set QUOTED_IDENTIFIER OFF, therby producing the maintenance plan failure.