INSERT failed because of incorrect SET options settings: 'ANSI_PADDING'.

Posted on 2012-09-20
Last Modified: 2012-09-25
While runing stored procedures, it failed. The error message is:

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

What would cause this error?
The database is not changed since last time.
No 'ANSI_PADDING' setting while creating stored procedures.
Question by:treehouse2008
    LVL 39

    Expert Comment

    by:Kyle Abrahams

    If ansi_padding is off the length of the column has to match the length of the input variable.  If you set it on, SQL will automatically pad blanks to the end so that it matches.
    LVL 11

    Accepted Solution

    It depends on the connection default settings.
    You can check the ansi_padding setting of your connection (or the wrong operational connection) in the sys.dm_exec_sessions view:
    select ansi_padding from sys.dm_exec_sessions
    where session_id = @@spid

    Open in new window

    Indexed views require the ANSI_PADDING is set to ON. More info about this:
    LVL 25

    Expert Comment

    LVL 68

    Expert Comment

    Add the required setting in the stored proc.  When the stored proc is over, it will revert back to the original caller's setting:

    SET ANSI_PADDING ON of proc code as before...

    Author Closing Comment

    'ANSI_PADDING' for one of connections is set as off, and this connection is used to run sp. Turn it on for this connection, the problem is solved.
    LVL 68

    Expert Comment

    Until the next connection that has it set off.

    Changing it in the proc fixes it for ALL connections, now AND in the future.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now