Access 2000 Linked char field has trailing space filling up the field

I'm migrating database tables to SQL 2000 from Access 2000.  I have a Description field that is defined in SQL as a char (500) field.  I've figured out that SQL 2000 automatically fills up the field with spaces, and for some reason that causes some odd things to happen.  Mainly, in a multi-line text box, I get line breaks in unexpected places.  Surprisingly enough, I'm NOT getting any errors saying I've entered too many characters or anything like that when I try to edit the field, but it is displaying strangely.  And I know that if I change the display using Trim() or RTrim I will have an un-editable control.

Is there a different way I should define the field in SQL or Access that will eliminate this trailing space?  Or something else I'm not considering?

Thanks in advance
Who is Participating?
Steve BinkCommented:
Yup, that would do it.  :)  The ANSI_PADDING setting is what I should have directed you to in the first place.  Here's the snippet from Books OnLine:

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.

Char(n) and binary(n) columns that allow nulls are padded to the length of the column when SET ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when SET ANSI_PADDING is OFF. Char(n) and binary(n) columns that do not allow nulls are always padded to the length of the column.

Important  It is recommended that ANSI_PADDING always be set to ON. SET ANSI_PADDING must be ON when creating or manipulating indexes on computed columns or indexed views.
Steve BinkCommented:
Does the data in the table have the extra spacing as well?  If so, you can run an UPDATE query to use Trim() on the actual data.

Also, have you tried changing the data type from char to varchar?
mcrowleyAuthor Commented:
I think I just figured it out by changing it to varchar and then setting my ODBC data source to NOT use ANSI paddings/nulls etc.
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.

All Courses

From novice to tech pro — start learning today.