Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    7

    Unanswered: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDEN

    I am getting this message:
    UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

    Only when updating a table that I recently put an Indexed View on. I have read all the white-papers I can find as to why this occurs, and they all say the same thing, that the QUOTED_IDENTIFIER needs to be enabled. I have done that as far as I can tell.

    I altered the database setting QUOTED_IDENTIFIER ON, which should affect every connection.

    Can someone point in the right direction?

    I am using SQL Server 2000 Enterprise Edition sp3

    Thanks,
    Eric

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    How are you applying the update.

    Try running the profiler and see what happens when the client connects or when the update is applied. You will might see the setting set off and will have to set it again for the connection.

  3. #3
    Join Date
    Dec 2002
    Posts
    7

    I figured it out....

    Maybe this can serve as a FAQ for other people new to Indexed Views. I probably would have caught this sooner, but the error that I was seeing on the webpage didn't give me the full picture. There was a trigger that was erroring out as well.

    Having said that, I was able to fix the problem by recompiling the stored procedure that was being called by the trigger.

    Also if anyone ever has this trouble, just remember to try recompiling with the following options set.

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
    GO

    On another note. If you are attempting to use your newly created Indexed View and for some reason the optimizer just won't select the correct index, recompile the stored proc that does the select using the above options set once again.

  4. #4
    Join Date
    May 2003
    Location
    Memphis, TN
    Posts
    1

    Question Current SP settings

    Is it possible to tell what settings a stored procedure was compiled with? I am having similar problems related to them not having the right SET options. I am wanting to find a way to query the ones that need to be changed.

    Thanks,
    Micky

  5. #5
    Join Date
    Dec 2002
    Posts
    7

    Question

    Well, I don't think there is a way to determine the actual set options for a specific stored procedure, but if you look at the dependencies, you should be able to determine what procs would be affected by an Indexed View and then just recompile them with the proper set options.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •