BUG: Load Table May Fail If Created with Different ANSI_PADDING (170639)
The information in this article applies to:
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:
- 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.
Modification Type: | Major | Last Reviewed: | 10/3/2003 |
---|
Keywords: | kbBug kbProgramming kbusage KB170639 |
---|
|