locked
SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING'. RRS feed

  • Question

  • Hi All,

    I have DDL Trigger on My Database. Now when i try to execute a create state with SET ANSI_PADDING OFF  i am getting the below error.

    Msg 1934, Level 16, State 1, Procedure utrg_TrgUsrDDL, Line 13

     

    SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

     

    I have come to know that this is because of DDL Trigger but can some one tell me what can be the solution of this one. Thank you


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com
    Tuesday, November 22, 2011 8:47 AM

Answers

  • Hi Hasham,

    Setting It to ON does work! But what if i want to set if OFF than any solution for it


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com


    HI !

    This is from MSDN link i have provided you;

    In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    http://msdn.microsoft.com/en-us/library/ms187403.aspx

    Also, post your trigger ocde here so i can comment on that.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    Tuesday, November 22, 2011 11:36 AM
    Answerer

All replies

  • Hi All,

    I have DDL Trigger on My Database. Now when i try to execute a create state with SET ANSI_PADDING OFF  i am getting the below error.

    Msg 1934, Level 16, State 1, Procedure utrg_TrgUsrDDL, Line 13

     

    SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

     

    I have come to know that this is because of DDL Trigger but can some one tell me what can be the solution of this one. Thank you


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com


    HI !

    SET the 'ANSI_PADDING' option to 'ON'. Also please post your Trigger code in here.

    For more details;

    http://msdn.microsoft.com/en-us/library/ms187403.aspx

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    Tuesday, November 22, 2011 10:27 AM
    Answerer
  • Hi Hasham,

    Setting It to ON does work! But what if i want to set if OFF than any solution for it


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com
    Tuesday, November 22, 2011 11:31 AM
  • Hi Hasham,

    Setting It to ON does work! But what if i want to set if OFF than any solution for it


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com


    HI !

    This is from MSDN link i have provided you;

    In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    http://msdn.microsoft.com/en-us/library/ms187403.aspx

    Also, post your trigger ocde here so i can comment on that.

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    Tuesday, November 22, 2011 11:36 AM
    Answerer
  • This is because you are working with XML type in the trigger.

    SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

    Wednesday, February 15, 2012 11:15 AM