SQL Server 2012 - SET ANSI_PADDING Setting and Its Importance

  • Article

It's time to feature another TechNet Wiki article! Check out this article from Naomi N:

 

SET ANSI_PADDING Setting and Its Importance

 

Problem Description

Recently I got an interesting escalation to solve for the client. Our VFP based application was getting the following SQL Server error: "Violation of PRIMARY KEY constraint 'rep_crit_operator_report'. Cannot insert duplicate key in object 'dbo.rep_crit' The duplicate key value is (ADMIN, REPORT_PERIOD_SALES)."

Resolution

To be honest, I should have guessed what was happening by myself. But I must admit that I still didn't, I sent e-mail to my colleagues asking what do they think about that strange behavior and I also posted this thread Weird problem with the client . My colleague immediately recognized the problem as one he already experienced with another client. And Latheesh NK  also pointed out into SET ANSI_PADDING setting as possible culprit.

So, somehow several tables were saved with the wrong ANSI_PADDING setting being in effect and therefore the column's setting overrode sessions settings.
Recently I made a change in our VFP applications to save varchar columns as varchar (prior to that all varchar columns were automatically padded with spaces to their length). This caused the above mentioned problem when the client upgraded the software to the recent release version.

The solution to that particular error was to run ALTER TABLE statement to alter report column to be the same width as the original column but using SET ANSI_PADDING ON before running the statement. This fixed the wrong padding on the column.

This is how we can check column's status in design mode when we right click on the column and check its properties:

ANSI Padding Status is close to the bottom in the designer.

After the problem was identified, we wanted to check the scope of the problem and also correct the problem for other columns that have been saved with wrong ANSI_PADDING setting.
 

See the full article here:

SET ANSI_PADDING Setting and Its Importance

 

 

Thanks to Naomi and Latheesh for this great solution!

- User Ed