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

Posted on 2005-05-05
Last Modified: 2010-03-19
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
Question by:mcrowley
    LVL 50

    Expert Comment

    by:Steve Bink
    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?
    LVL 2

    Author Comment

    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.
    LVL 50

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how the fundamental information of how to create a table.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now