Post reply

''QUOTED_IDENTIFIER'' error

  • April 3, 2007 at 5:09 pm

    #62370

    We have this job which is simply executes DBCC CHECKDB command and sometimes returns an error:

    Executed as user: user\service. DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. [SQLSTATE 42000] (Error 1934).  The step failed.

    Should we run DBCC CHECKDB as a stored procedure and set QUOTED_IDENTIFIER ON or there is another reason for this error?

    Thanks

  • April 3, 2007 at 8:16 pm

    #698448

    By default QUOTED IDENTIFIER IS SET TO OFF.

    Are you sure that prior to executing DBCC CHECKDB that this option isn't set to ON?

    Kindest Regards,

  • April 3, 2007 at 11:25 pm

    #698463

    No, i am not sure.

    We have 4 steps in a job like:

    step 1: dbcc checkdb (checks 1st db&nbsp -- this step always success

    if all ok then

    step 2 : dbcc checkdb ( checks 2nd db) -- here is where the job fails

    step 3... 

  • April 4, 2007 at 7:05 am

    #698524

    What are the DB names?

  • April 4, 2007 at 9:58 am

    #698583

    More than likely, you are getting this problem because you have a computed column or indexed view on a table.  There are specific options that have to be set correctly with the SET command.

    From SQL Server 2000 Books Online:

    "SET Option Settings

    Any SET options that affect the results generated by Transact-SQL statements must have the same settings for all operations referencing the index. There are seven SET options that affect the results stored in computed columns and returned by views. All connections using indexes on computed columns or indexed views must have the same settings for these seven options:

    These six SET options must be set to ON:

    ANSI_NULLS

    ANSI_PADDING

    ANSI_WARNINGS

    ARITHABORT

    CONCAT_NULL_YIELDS_NULL

    QUOTED_IDENTIFIER

    The NUMERIC_ROUNDABORT option must be set to OFF."

     

     

  • April 4, 2007 at 11:25 am

    #698612

    Thank you so much

Viewing 6 posts - 1 through 5 (of 5 total)

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