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 translations Article translations
Article ID: 928962 - View products that this article applies to.
Expand all | Collapse all

On This Page

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 "Symptoms" 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 "Symptoms" 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 "Applies to" section.

MORE INFORMATION

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.
  4. If you are prompted, click the instance of SQL Server in the list, and then click OK.
  5. In Solution Explorer, right-click the project, point to Add , click Stored Procedure, and then click Add .
  6. Replace any existing code with the code example that is listed in the "Symptoms" section.
  7. On the Build menu, click Build Solution.

    You receive warning messages that resemble the warning messages that are mentioned in the "Symptoms" section.

Properties

Article ID: 928962 - Last Review: December 22, 2006 - Revision: 2.0
APPLIES TO
  • Microsoft Visual Studio 2005 Team Edition for Database Professionals
Keywords: 
kbpending kbbug kberrmsg kbpubtypekc kbexpertiseadvanced kbtshoot KB928962

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com