Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/170639

From BetaArchive Wiki
Knowledge Base


BUG: Load Table May Fail If Created with Different ANSI_PADDING

Article ID: 170639

Article Last Modified on 10/3/2003



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q170639

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:

  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.


Additional query words: script schema DMO ::Script

Keywords: kbbug kbprogramming kbusage KB170639