FIX: AV on INSERT of UNIONed SELECTs to Table With DEFAULTs

Last reviewed: April 8, 1997
Article ID: Q149938

The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 6.5
BUG #: 14833 (6.00 and 6.50)

SYMPTOMS

INSERTing to a table from a UNION of two or more SELECT statements can result in an Access Violation.

CAUSE

At a certain threshold of data, a work table needs to be created to resolve the UNION correctly. If the target table has DEFAULTs, these are not applied to intermediate work tables but other attributes of the target table, such as NOT NULL, are applied, and this would cause the query to fail with a different error if there was not the AV.

WORKAROUND

Implement the INSERT as distinct INSERT SELECTs for each table in the UNION. If there is a UNIQUE INDEX on the target table, use the IGNORE_DUP_KEY option on that INDEX to filter out duplicate rows that would previously have been filtered out by the UNION. In many cases, this method can be much faster anyway because it can eliminate one or more intermedate work tables.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.


Additional query words: sql60 sql65 union insert select av
Keywords : kbbug6.00 kbbug6.50 kbfix6.50.sp1 kbprg kbusage SSrvProg
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kberrmsg


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: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.