QUOTED_IDENTIFIER Error When Running an ALTER INDEX Job

Question

QUOTED_IDENTIFIER Error When Running an ALTER INDEX Job

Buxton69-502449

SSCrazy

Points: 2026
More actions
November 26, 2007 at 3:01 am

#153979

I'm running the following from within a job:
sp_MSForEachTable "ALTER INDEX ALL ON ? REBUILD"
This works for other databases I have but on one database I get the following error after 15 minutes:
ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934).
Does anyone know what I can do to get this to work, I tried to put SET QUOTED_IDENTIFIER ON into the job but it still failed on the same thing and then I tried to set it off but it still failed.
I wrote the output to a text file but it still told me no more than the above error message.
Thanks

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Check your indexed views and whether they were created with ansi nulls and quoted identifiers set to on.

You can get this information from sys.sql_modules (on 2005) or by using the OBJECTPROPERTY function with 'ExecIsAnsiNullsOn' and 'ExecIsQuotedIdentOn'

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 2

hi Busxton 67,

The following is from BOL:

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views .

Regards,

Ahmed

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 3

Sorry

I missed to tell you When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled

Regards,

Ahmed

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 4

It's not the views, sp_MSforEachTable only works on tables.

I don't think it's the way that the indexes were created although that is very interesting to know, I think it's the sp_MSforEachTable stored procedure, I just ran the ALTER INDEX command individually for each index in the database and it worked fine, I don't know why this works for some databases and not others but that's probably why Microsoft chose not to document it.

Thanks anyway, I think I might have to go back to the self written iterate.

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 5

Hi Buxton69

Check the following code http://www.mssqltips.com/tip.asp?tip=1367 (good one)

regards,

Ahmed

abmore Hall of Fame Points: 3628 More actions · Answer 6

hi !,

Try this once.

EXECUTE sp_msforeachtable 'print "?" dbcc DBREINDEX ("?")'

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 7

Ahmed Bouzamondo (11/26/2007)
Hi Buxton69
Check the following code http://www.mssqltips.com/tip.asp?tip=1367 (good one)
regards,
Ahmed

Ahmed, thanks for the link, I think I'll use that, it looks like someones just adopted the Microsoft style, if you look at the code for sp_MSforEachTable they are very similar.

Abhijit, according to BOL DEREINDEX shouldn't be used anymore, printing out the commands would not be much good to me from within a job.

noeld SSC Guru Points: 96590 More actions · Answer 8

Buxton69 (11/26/2007)
It's not the views, sp_MSforEachTable only works on tables.
I don't think it's the way that the indexes were created although that is very interesting to know, I think it's the sp_MSforEachTable stored procedure, I just ran the ALTER INDEX command individually for each index in the database and it worked fine, I don't know why this works for some databases and not others but that's probably why Microsoft chose not to document it.
Thanks anyway, I think I might have to go back to the self written iterate.

INDEXED VIEWS are schema bounded to the underlying tables. Therefore when you touch the tables the "indexed" views could "potentially" be touched too. Definitely COMPUTED COLUMNS that are persisted also fall on this category and BOTH require those ANSI Settings 😀

* Noel