Microsoft KB Archive/296559

= PRB: ALTER COLUMN Sets TrimTrailingBlanks Property to NO =

Article ID: 296559

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q296559



SYMPTOMS
When altering an existing varchar column on a table, no matter what the setting is for the ANSI PADDING option at the time of alteration, the TrimTrailingBlanks property for the altered column is always set to NO.

This behavior may potentially break applications that are sensitive to padded spaces.



CAUSE
This behavior is by design. ANSI NULL defaults and ANSI PADDING are always set to ON for an ALTER COLUMN statement. For more information, refer to the &quot;ALTER TABLE (T-SQL)&quot; topic in SQL Server Books Online.



WORKAROUND
Re-create the table and transfer existing data.



Steps to Reproduce Behavior
  Run the following script: set ansi_padding off go create table one (a int, b varchar(50) not null, c varchar(10)) go

 Run the sp_help one stored procedure and it returns:

  Run the following script to alter the nullability of column b. alter table one alter column b varchar(50) null

 Run the sp_help stored procedure one. Note that even though you only wanted to change the nullability, the TrimTrailingBlanks property also changed. 

Additional query words: blank empty padded padding alter table modify change

Keywords: kbprb KB296559

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.