ANSI PADDING IS SET TO OFF

Question

ANSI PADDING IS SET TO OFF

PAUL STANLEY

Mr or Mrs. 500

Points: 539
More actions
April 8, 2006 at 7:06 am

#365847

Hi
I have set set the option in my database using sp_dboptions to Ansi padding off and checked it to make sure. However when I insert a new record into a table with a field of data type varchar(100) it is still padding with trailing blanks.

I am a beginner with SQL Server so might be something straight forward.
Thanks for any help.
Paul

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 1

A lot these options can be set when the connection to the database is created and will overide the deafult options set on the database

You want to check the connection properties in managment studio for query execution. These can be found under tools->options. Expand the query execution options and then sql server ones and click on the ansi option its likely that when you do this that you'll see that when management studio connects it is settting the ANSI PAdding on

hth

David

PAUL STANLEY Mr or Mrs. 500 Points: 539 More actions · Answer 2

Thanks David but not sure what the management studio is. However, I am still using VB6 and will check the connection properties in that. Thanks for your help.

Paul

PhilPacha Hall of Fame Points: 3645 More actions · Answer 3

I'm not certain about 2005 but, in 2000, what matters is the ANSI_PADDING setting in effect on a connection when the column was created, not the current setting. Here's an extract from BOL (note the final sentence.)

When set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When set to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns.

Therefore, if a column was defined with ANSI_PADDING ON, it is always ON for that column. Use an RTRIM() to remove trailing blanks.

You can confirm this (at least in 2000) with:

SELECT COLUMNPROPERTY(OBJECT_ID('YourTable', 'YourColumn, 'UsesAnsiTrim')

If this returns 1, then ANSI padding was ON when the column was created.

PAUL STANLEY Mr or Mrs. 500 Points: 539 More actions · Answer 4

Yes thanks for your answer.

I managed to rectify the problem. The table column in question was varchar(100) but the stored procedure I was using excepted parameters which were char(100). When I set this parameter to varchar(100) the column was not padded. Thanks for your help.

Paul

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 5

That actually doesn't change the settings of the table - the table will still store trailing spaces because ANSI PADDING is still on for the table. As others noted, this is affected by the connection settings when the table was created. I believe that SSMS still turns it on when you create a table through the GUI - the same as Enterprise Manager did. No override that I'm aware of, even when we have specified ANSI PADDING OFF elsewhere. 🙁

Only way I've seen to fix this is to re-create the table and ensure the settings of your connection have ANSI PADDING OFF. I seem to remember that ODBC connections were especially bad about passing in trailing spaces by default - usually to the max length of the field. 😛

-Pete