rebuild index error due to Quoted Identifier set option

Question

rebuild index error due to Quoted Identifier set option

Ricardo Lopez

SSC-Addicted

Points: 491
More actions
December 9, 2004 at 10:50 pm

#86111

my scheduled db optimization job (associated with my db maintannace plan) is getting the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
I tried adding a new step that would execute prior to the step : EXECUTE master.dbo.xp_sqlmaint ... (master db specified)
The new step consisted of SET QUOTED_IDENTIFIER ON (user db I'm trying to optimize specified).
I still got the same error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Should I modify SET QUOTED_IDENTIFIER ON step so that master db is specified ??????
Should I only have one step consisting of: SET QUOTED_IDENTIFIER ON followed by EXECUTE master.dbo.xp_sqlmaint ... (master db specified) ???
--DBA newbie

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tim Hetherington SSC Enthusiast Points: 157 More actions · Answer 1

Is this on the integrity checks?

Try taking them out of the maintenance plan and schedule your own job to do the dbcc checkdb. You can then set the needed ansi settings in the t-sql for the job:

SET ARITHABORT OFF

SET QUOTED_IDENTIFIER OFF

DBCC CHECKDB ('YourDatabase')

By default, quoted_identifier and arithabort aren't set with

SQL Agent. When you run a dbcc checkdb or checktable on

databases/tables that have computed columns with indexes,

you need to have both settings on. When creating the jobs

where there are the computed columns, you have to explicitly

set the settings in the job.

Tim Hetherington

A.K.A. White Knight

Ricardo Lopez SSC-Addicted Points: 491 More actions · Answer 2

The scheduled job that is failing is associated with the Optimization tab of the DB Maintenance property windows; The job that is failing has nothing to do Integrity.

Thanks for your reply.

Ricardo Lopez SSC-Addicted Points: 491 More actions · Answer 3

I found an almost complete description of a fix of this problem at: http://kerblog.com/earlyedition/archive/2004/11/25/247.aspx

I replied with what I will attempt and asked my remaing question concerning fix:

I have a similar problem with Optimizations Job but my error message is : [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

Please verify my interpretation of your solution and answer one detail:

I will create a new job step that will preceed the job step associated with : EXECUTE master.dbo.xp_sqlmaint ...

The new job step will consist of :

SET QUOTED_IDENTIFIER ON

exec sp_updatestats

Should the new job step specify the MASTER database or the user database I'm trying optimize????

---------------------------------------------------------

Has anyone else used above fix???? Feedback is desired.

--paranoid newbie DBA

Ricardo Lopez SSC-Addicted Points: 491 More actions · Answer 4

the solution described in the link below is complete!! I was a little tired when I read it initially. I went back re-read and found it had all I needed. I will let solution run this weekend.

http://kerblog.com/earlyedition/archive/2004/11/25/247.aspx

Plamen Kouzov SSC Enthusiast Points: 153 More actions · Answer 5

according to http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20293276.html

there is a bug in sqlmain.exe.

As I workaround, here is a script to reindex all user tables in a SQL Server (all databases)

declare @dbid int

declare @reindexdbcommand varchar(128)

declare @ServerDBs table(dbid smallint, reindexdbcommand varchar(128))

insert into @ServerDBs (dbid, reindexdbcommand)

select dbid, 'use ' + [name] + '; EXEC sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'')";'

from master.dbo.sysdatabases

where [name] not in ('master','tempdb','model','msdb','pubs','Northwind')

while (select count(*) from @ServerDBs)>0

BEGIN

set @dbid = (select TOP 1 dbid from @ServerDBs)

set @reindexdbcommand = (select TOP 1 reindexdbcommand from @ServerDBs where dbid = @dbid)

exec sp_sqlexec @reindexdbcommand

delete from @ServerDBs where dbid = @dbid

END

Jeff Brumley Right there with Babe Points: 752 More actions · Answer 6

Bless you - that script worked like a champ. I couldn't run sp_MSforeachtable with DBCC REINDEX because the ? was putting in [dbo].[databasename] and the syntax was messing it up. Your script worked perfectly.

phani kumar gotety Ten Centuries Points: 1153 More actions · Answer 7

Microsoft acknowledged this and gave a small workaround to this. They say this problem occurs when the database contains a table that has an index on a computed column.

http://support.microsoft.com/kb/902388/

The catch here is the server needs SP4 to be installed .

Plamen:

Indeed your script works great. Thanks a ton for that.

I just made a small change to make it work for one database ( as per my requirement)