BUG: Select with ANSI Joins Against View with Union Operator and ANSI Joins Fails with Error 4409

• Microsoft SQL Server version 6.5 Service Pack 5 and 5a

SYMPTOMS

A SELECT statement, using ANSI-standard joins against a view defined using ANSI-standard joins and the UNION operator, may fail with this error:

Msg 4409, Level 20, State 1
The columns in the query definition and the view definition do not match.

WORKAROUND

Here are three workarounds:

• Change the query to use TSQL join syntax.
  
  -or-



• Change the view definition to use TSQL join syntax.
  
  -or-



• Remove the UNION operator from the view definition.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.416.

MORE INFORMATION

The following scenario demonstrates this problem:

CREATE TABLE dbo.Assignment (
BUID int NULL 
)
GO

CREATE TABLE dbo.Project (
ProjectID int NULL ,
BUID int NULL
)
GO

CREATE TABLE dbo.ProjectActual (
ProjectID int NULL 
)
GO

CREATE VIEW V_UpdateProject AS
SELECT Project.ProjectID
FROM Project INNER JOIN Assignment ON Project.BUID = Assignment.BUID
UNION 
SELECT Project.ProjectID
FROM Project
GO

SELECT V_UpdateProject.ProjectID
FROM V_UpdateProject
INNER JOIN ProjectActual ON V_UpdateProject.ProjectID = ProjectActual.ProjectID 
go 

Msg 4409, Level 20, State 1
The columns in the query definition and the view definition do not match.

Additional query words:

Keywords : SSrvTran_SQL kbSQLServ650sp5bug
Version : winnt:6.5 Service Pack 5 and 5a
Platform : winnt
Issue type : kbbug