Use of QUOTED_IDENTIFIER to overcome 'ALTER INDEX failed' error

Question

Like others previously, today in my SQL logs I can see that a scheduled job has failed to execute due to Error 1934.

ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

After reading here, here and here, I still feel unsure about whether my hack (below) is introducing risk based upon advice given elsewhere. My lack of experience is the main driver for this question.

The original code is:

DECLARE @Database varchar(255);
DECLARE @Table varchar(255);
DECLARE @cmd nvarchar(500);
DECLARE @fillfactor int = 90;

DECLARE DatabaseCursor CURSOR FOR
SELECT
    name
FROM
    MASTER.dbo.sysdatabases
WHERE
    name IN ('MyDbName')
ORDER BY
    name;

OPEN DatabaseCursor;
FETCH NEXT FROM DatabaseCursor INTO @Database;
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
      table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
      WHERE table_type = ''BASE TABLE''';

        -- create table cursor  
        EXEC (@cmd);
        OPEN TableCursor;
        FETCH NEXT FROM TableCursor INTO @Table;
        WHILE (@@FETCH_STATUS = 0)
            BEGIN
                SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(varchar(3), @fillfactor) + ')';

                EXEC (@cmd);
                FETCH NEXT FROM TableCursor INTO @Table;
            END

        CLOSE TableCursor;
        DEALLOCATE TableCursor;
        FETCH NEXT FROM DatabaseCursor INTO @Database;
    END
CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;

USE MyDbName;
GO
EXEC sys.sp_updatestats;

I believe that this error began occuring after an index was added to an XML column in one particular table, but I'm not 100% sure. Elsewhere we also have stored procedures that use XPATH. I know others have experienced the same error, but without the experience of manually setting QUOTED_IDENTIFIER, I'm looking for advice plus a way to improve my fix.

Based on what I've read, this revised code does execute without error:

 SET @cmd = 'SET QUOTED_IDENTIFIER ON; ' + 
            'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(varchar(3), @fillfactor) + ')';

My concerns are:

Does this change introduce any risk or other danger that is not immediately apparent (just because it runs without errors doesn't settle my nerves!)
If not, is there a way to identify exactly what causes this (e.g. a parituclar index or DB column/procedure)?
If so, can that be accounted for in the dynamic SQL and more elegantly dealt with?

Thank you in advance for sharing your expertise.

score 3 · Accepted Answer · answered Oct 31 '16 at 12:01

No, there is no risk.
Catch the error using TRY / CATCH block, so you can log the variables' contents somewhere.
You can change default connection settings in the server properties so that QUOTED_IDENTIFIER will always be on. However, it's not a 100% guarantee, since data access drivers can override connection settings (different drivers - ODBC, OLE DB, Native Client, etc. - have slightly different defaults).

score 2 · Answer 2 · answered Oct 31 '16 at 11:57

2

Please set the quoted_identifier option to ON then test again

SET QUOTED_IDENTIFIER ON

answered Oct 31 '16 at 11:57

Eralper

6,461
2
21
27

Where abouts should I do this, at the top of the code block? It's already set in the dynamic code in my *fix* which does run without issue. – EvilDr Oct 31 '16 at 11:59
1

@EvilDr, you could run the SET statement as the first statement in your script instead of in each dynamic statement. – Dan Guzman Oct 31 '16 at 12:14
1

Yes, using it as the first statement will be safe – Eralper Oct 31 '16 at 12:23
You can also use QUOTENAME() function in dynamic statements surrounding table names, etc. – Eralper Oct 31 '16 at 12:24
this is absolutely true, had the same problem with stored procedures – krtek Oct 31 '16 at 12:29

Use of QUOTED_IDENTIFIER to overcome 'ALTER INDEX failed' error

2 Answers2