BUG: NULL Padding Behavior Different on Subscriber

Last reviewed: July 11, 1997
Article ID: Q171285

The information in this article applies to:

Microsoft SQL Server, version 6.5

BUG #: 17047 (Windows NT: 6.5)

SYMPTOMS

Subscriber tables may be created with ANSI_PADDING turned ON, sometimes causing undesirable behavior with NULLable fixed-length columns (char, binary, and so on) at the subscriber. This may also be perceived as incorrect data at the subscriber. The data that is copied over during the initial synchronization does not have this problem because it uses DB-Library to connect to the subscriber to apply the BCP data and the default behavior for these SET options is OFF.

CAUSE

In SQL 6.5, the ODBC driver (build 2.65.0240) automatically sets the following option:

   SET ANSI_DEFAULTS ON

This option enables the ANSI_PADDING option. This causes the tables at the subscriber to exhibit this behavior even if ANSI_PADDING is turned OFF during INSERT operations.

WORKAROUND

The workaround for this problem is to edit the schema files (.sch files in the replication working directory) immediately after creating the publication to include the following statements before the CREATE TABLE statement:

   SET ANSI_DEFAULTS OFF
   go

At the end of the script, after the CREATE statements, add the following statements:

   SET ANSI_DEFAULTS ON
   go

This reverts back to ANSI standard behavior for the other statements to follow. If you do not add the SET ANSI_DEFAULTS ON statement, subsequent statements may fail because the replication process expects this option to be turned ON.

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: blank spaces suffixed dblib db-lib
Keywords : kbbug6.50 kbinterop kbprg SSrvRep SSrvTran_SQL
Version : 6.5
Platform : WINDOWS
Issue type : kbbug
Resolution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.