[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2676
  • Last Modified:

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

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.
0
treehouse2008
Asked:
treehouse2008
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
http://msdn.microsoft.com/en-us/library/ms187403(v=sql.90).aspx

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.
0
 
Máté FarkasDatabase Developer and AdministratorCommented:
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: http://msdn.microsoft.com/en-us/library/ms175088(v=sql.90).aspx
0
 
TempDBACommented:
.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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


CREATE PROCEDURE ...
AS
SET NOCOUNT ON
SET ANSI_PADDING ON
...rest of proc code as before...
0
 
treehouse2008Author Commented:
'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.
0
 
Scott PletcherSenior DBACommented:
Until the next connection that has it set off.

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now