Microsoft KB Archive/219413

= FIX: Select on a View May Generate Error 4409 =

Article ID: 219413

Article Last Modified on 10/7/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q219413



SYMPTOMS
If you execute a SELECT statement and in the FROM clause you have a view that is aliased twice or more, and this VIEW has a UNION in its definition, you may encounter the following error message:

Msg. 4409, Level 20, State 1

The columns in the query definition and the view definition do not match.



RESOLUTION
A supported fix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Apply it only to computers that are experiencing this specific problem. This fix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

Files
  Date        Time         Version    Size    File name      Platform 1/29/1999  4:04:20 PM   6.50.422   2.43MB  SQLSERVR.EXE   i386 & Alpha 1/29/1999  3:10:06 PM   6.50.422   147 KB  OPENDS60.DLL   i386 & Alpha

NOTE: Due to file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.



WORKAROUND
To work around this problem create the view by selecting all the columns from the tables.



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



MORE INFORMATION
For example, consider a VIEW defined as follows in the PUBS database:

CREATE VIEW writers AS SELECT 'LastName' = au_lname , 'FirstName' = au_fname , 'Address'    = address FROM pubs.dbo.authors UNION SELECT 'LastName' = au_lname , 'FirstName' = au_fname , 'Address'    = '' FROM pubs.dbo.authors GO If the following statement is executed, it gives the above mentioned error message: SELECT w.Firstname, w1.LastName FROM writers w, writers w1 WHERE w.firstname = w1.firstname AND w.lastname = w1.lastname To work around this problem change the VIEW definition as follows: CREATE VIEW writers AS SELECT 'Id' = au_id , 'LastName' = au_lname , 'FirstName' = au_fname , 'Phone No' = phone , 'Address' = address , 'City' = city , 'State' = state , 'Zip Code' = zip , 'Contract type' = contract FROM pubs.dbo.authors UNION SELECT 'Id' = au_id , 'LastName' = au_lname , 'FirstName' = au_fname , 'Phone No' = phone , 'Address' = '' , 'City' = '' , 'State' = '' , 'Zip Code' = zip , 'Contract type' = contract FROM pubs.dbo.authors GO

Additional query words: select view error

Keywords: kbbug kbfix kbqfe kbcode KB219413

-

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

© Microsoft Corporation. All rights reserved.