BUG: Load Table May Fail If Created with Different ANSI_PADDING

• Microsoft SQL Server, version 6.5

SYMPTOMS

An attempt to load a table may fail with the following error message:

   Msg 8412, Level 16, State 3
   Schemas differ between source table '' and target table 'load_test'.
   Table load has been aborted for table 'load_test'.

• The table has a nullable char or varchar column and was created under one ANSI_PADDING setting.
• The table was dumped, dropped, and re-created from a script, but with a different ANSI_PADDING option set.

The above behavior will most commonly be seen if the following sequence of events has occurred:

1. An ODBC-based tool, such as the Microsoft Access Upsizing Wizard, is used to originally create the tables

2. The schema is scripted using SQL Enterprise Manager and used to re-create the table at a later point in time from a DB-Library based client application (such as ISQL or ISQL/w). ODBC applications set ANSI_PADDING on by default, whereas DB-Library clients do not typically have this option set.

WORKAROUND

When re-creating the table, add an explicit call to SET ANSI_PADDING ON or OFF as appropriate. If the table was originally created from an ODBC client and you are trying to re-create it from a DB-Library client, set the option ON. If you are trying to do the reverse, set the option to OFF.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.