Post reply

Setting Database level defaults

  • July 12, 2007 at 3:27 pm

    #117771

    Guys,

    Each time I try to create a stored proc I get the following error.

    Msg 1934, Level 16, State 1, Procedure ddlDatabaseTriggerLog, Line 17

    SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, 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.

    I ran following statements to set the defaults, but didnt seem to work either.

    Alter Database MASTER SET arithabort On

    Alter Database MASTER SET ANSI_NULLS On

    Alter Database MASTER SET CONCAT_NULL_YIELDS_NULL On

    Alter Database MASTER SET QUOTED_IDENTIFIER On

    Any suggestions and inputs would help

    Thanks

  • July 12, 2007 at 3:58 pm

    #719123

    >Each time I try to create a stored proc I get the following error.

    What type of trigger are you trying to create. i.e. DDL or DML, when you get the error.

    Everything you can imagine is real.

  • July 12, 2007 at 7:45 pm

    #719150

    You may want to alter your user database settings and not master's.

    The error message says that there is a DDL trigger "Procedure ddlDatabaseTriggerLog" build for your user database that records all DDL changes; the error is thrown from within this DDL trigger and is not a bug into your sp you're trying to create. If you do not see it that means you do not have permissions for this; You would need to ALTER ...the above settings for your user database, otherwise the error will continue to appear on the DDL trigger.

     

     

  • July 12, 2007 at 11:40 pm

    #719175

    You have to alter your connection settings properties in QA and no the database settings. Change your connection property then you will avoid these.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 13, 2007 at 7:18 am

    #719281

    Thanks guys, there is nothing wrong with the database defaults. But the I had to disable the database level trigger - ddldatabasetriggerlog which was causing the problem.

    Thanks again

  • July 13, 2007 at 9:36 am

    #719338

    Why disable the database level trigger? Why not fix the underlying cause? Did you re-enable the trigger after getting your SP in?

  • July 16, 2007 at 2:03 am

    #719579

    Might be adding the connection setting options to the trigger and enabling them would solve the issue.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

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

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