Microsoft KB Archive/928962

= BUG: The Visual Studio 2005 interpreter cannot determine whether an ambiguous column belongs to an inner or outer SELECT statement in Visual Studio 2005 Team Edition for Database Professionals =

Article ID: 928962

Article Last Modified on 12/22/2006

-

APPLIES TO


 * Microsoft Visual Studio 2005 Team Edition for Database Professionals

-



SYMPTOMS
When you use code that includes an ambiguous column in Microsoft Visual Studio 2005 Team Edition for Database Professionals, the Visual Studio 2005 interpreter cannot determine whether the column belongs to an inner or outer SELECT statement. For example, you try to run the following code example. ---BEGIN--- CREATE PROCEDURE dbo.test AS CREATE TABLE T1 ( a int, b int, c int NULL) CREATE TABLE T2 ( a int, b int)

SELECT T1.a    FROM T1     WHERE T1.b NOT IN ( SELECT a               FROM T2               WHERE T2.a = T1.a AND c IS NULL) END When you do this, you receive warning messages that resemble the following.

Warning message 1

Warning 5 TSD3006: The relationship from [Procedure] dbo.test cannot be resolved between the following possible identifiers: [Column] dbo.T1.T1, [Column] dbo.T1.a, [Column] dbo.T2.T1.

Warning message 2

Warning 7 TSD3012: The following dependencies are missing from your database project: [dbo].[T1], [dbo].[T1].[a], [dbo].[T1].[b], [dbo].[T1].[c], [dbo].[T2], [dbo].[T2].[a], [dbo].[T2].[b]. Your database application might fail at runtime when [dbo].[test] is executed.



CAUSE
This issue occurs because an unqualified column name appears in a sub-select statement. The Visual Studio 2005 interpreter cannot determine whether the column belongs to the inner SELECT statement or to the outer SELECT statement. For example, the interpreter cannot disambiguate between T2.a and T1.a in the SELECT a statement that is in the &quot;Symptoms&quot; section code example. Therefore, the interpreter treats the column as an ambiguous column. The same issue occurs with the c IS NULL statement. In that case, the column is also flagged as ambiguous.



WORKAROUND
To work around this issue, you must fully qualify column names in Visual Studio 2005 database code. For example, rewrite the code example that is listed in the &quot;Symptoms&quot; section to resemble the following code example. ---BEGIN--- CREATE PROCEDURE dbo.test AS CREATE TABLE T1 ( a int, b int, c int NULL) CREATE TABLE T2 ( a int, b int)

SELECT T1.a          FROM T1           WHERE T1.b NOT IN ( SELECT T2.a               WHERE T2.a = T1.a AND T1.c IS NULL) END



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.



Steps to reproduce the problem

 * 1) Start Visual Studio 2005 Team Edition.
 * 2) On the File menu, point to New, and then click Project.
 * 3) Under Database Projects, click Microsoft SQL Server, and then double-click SQL Server 2005.

Note To select the correct project type, you must have Visual Studio 2005 Team Edition for Database Professionals installed.
 * 1) If you are prompted, click the instance of SQL Server in the list, and then click OK.
 * 2) In Solution Explorer, right-click the project, point to Add, click Stored Procedure, and then click Add.
 * 3) Replace any existing code with the code example that is listed in the &quot;Symptoms&quot; section.
 * 4) On the Build menu, click Build Solution.

You receive warning messages that resemble the warning messages that are mentioned in the &quot;Symptoms&quot; section.

Keywords: kbpending kbbug kberrmsg kbpubtypekc kbexpertiseadvanced kbtshoot KB928962

-

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

© Microsoft Corporation. All rights reserved.