BUG: Load Table May Fail If Created with Different ANSI_PADDING

ID: Q170639

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 17012 (NT: 6.50)

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'.

This problem occurs if both of the following conditions are true:

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.

Notice in the error message that the State is 3 in this scenario. Other state values indicate true schema differences between the source and destination tables.

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

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

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.

Additional query words: script schema DMO ::Script


Keywords          : kbprg kbusage SSrvAdmin SSrvDB_Lib SSrvDMO SSrvProg SSrvTran_SQL kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 16, 1999