Microsoft KB Archive/166201

= BUG: AV When INSERT SELECT UNION into a Table with a Foreign Key =

Article ID: 166201

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q166201



BUG #: 16712



SYMPTOMS
Using an INSERT INTO SELECT UNION statement to a table with a foreign key constraint may cause an access violation (AV). The following script demonstrates the problem:

CREATE TABLE dog (     a INT,      b INT   ) GO

ALTER TABLE dog ADD CONSTRAINT PKDog PRIMARY KEY (a) GO

CREATE TABLE cat (     b INT,      c char(1)   ) GO

ALTER TABLE cat ADD CONSTRAINT PKCat PRIMARY KEY (b) GO

ALTER TABLE dog ADD CONSTRAINT FKCat_Dog FOREIGN KEY (b) REFERENCES cat GO

INSERT INTO cat SELECT 1, 'a'  GO

INSERT INTO dog (a, b)     SELECT 1,1 UNION SELECT 2,1 GO

On the client side, the application will receive the following error:

DB-Library Process Dead - Connection Broken



WORKAROUND
To work around this problem, create a temporary table to hold the result set from the UNION statement, then INSERT INTO a target table by selecting data from temporary table. The following script demonstrates the workaround for the above scenario:

CREATE TABLE #temp (     a INT,      b INT   ) GO

INSERT INTO #temp SELECT 1,1 UNION SELECT 2,1 GO

INSERT INTO dog (a, b)     SELECT * FROM #temp



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.0 and 6.5.

Keywords: kbbug kbpending kbusage KB166201

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.