how do I find which columns have ANSI PADDING set to ON ?

Posted on 2009-05-28
Medium Priority
Last Modified: 2012-06-27
Hi.  I have an MSSQL 2000 table, and whenever I try to make a structure change, I get the warning:
Warning: Columns have different ANSI_PADDING settings. New columns will be created with ANSI_PADDING 'on'.

I have ANSI_PADDING set to OFF on the entire database.  I only have the problem with this one table.  I assume that somehow a column has its ANSI_PADDING set to OFF (if that is even possible to set at the column-level).  

How do I see which column has it set to ON, and then how would I change it?

-- thanks
Question by:Rob Rudloff
LVL 17

Accepted Solution

pssandhu earned 500 total points
ID: 24494955
Try this to check:
SELECT COLUMNPROPERTY(OBJECT_ID('YourTable', 'YourColumn, 'UsesAnsiTrim')
If this returns 1, then ANSI padding was ON when the column was created.
I guess the way to fix it would be tranfer data into a temp table... delete the original table.. recreate it the option set to OFF and insert the records back from the temp table.

Author Comment

by:Rob Rudloff
ID: 24495569
thanks pssandhu --
I'll give that a try.  I might just do the "fix", rather than see which fields have the problem.  As I look closer, I am finding more tables with this problem.  I wonder if there an easy way to switch all my fields on all my tables to ANSI_PADDING = off.
It sounds like I need to upgrade my programs -- according to  Microsoft, ANSI_PADDING is going away in some future release of SQL Server.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

597 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