CODEWITHSUNDEEP
sql-serversql-server-data-toolscreate-view
SZILÁRD ÁDÁM
SZILÁRD ÁDÁM

Reputation: 13

How can I fix SSDT marking random columns in my CREATE VIEW statement "ambiguous"?

I'm moving a bunch of SQL Server databases into SSDT projects so I can have source control. There is a view with a very large number of columns that prevent me from building one of the projects.

Project structure:

Solution: ServerName
|- DB1
   |- Views
      |- View_that_doesn't_build
      |- View_source_1
|- DB2
   |- Views
      |- View_source_2
|- ...

In DB1, I have added DB2 as a Database reference with the "different database, same server" option as the variable $(DB2).

The view looks something like this:

CREATE VIEW "View_that_doesn't_build" -- don't worry, it's actually a valid sysname, renamed for the sake of question clarity
AS
SELECT
  -- 143 columns, some computed, some normal
FROM
  [$(DatabaseName)].dbo.View_source_1
  INNER JOIN
  [$(DB2)].dbo.View_source_2
  ON View_source_1.code = View_source_2.code

After building, most of the columns don't report any error, but on some of them (usually the last X column) this error appears:

SQL71561: Computed Column: [dbo].[View_that_doesn't_build].[Some_column] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [$(DB2)].[dbo].[View_source_2].[Some_column] or [dbo].[View_source_1].[Some_column].

The problem is, [Some_column] doesn't exist in both source views, because except for "code" (which doesn't give such an error) every column name is unique in the two source views.

What I've tried so far:

I even checked if the statement runs in SSMS, it does.

How can I fix this build error? Does it happen because of the large number of columns?

EDIT: I've not been very clear before, when aliasing the tables, I've also qualified all columns with the table alias, same error occures.

Upvotes: 0

Views: 313

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

SSDT doesn't support 3 part names referencing objects in the current database. Use 2-part names instead: dbo.View_source_1 instead of [$(DatabaseName)].dbo.View_source_1.

If you need 3-part names for some reason, create synonyms for the 3-part names and reference the 2-part synonym instead.

Upvotes: 2

Thom A
Thom A

Reputation: 95561

The error is telling you the problem here. As you've not actually given us any meaningful DDL then I can't "fix" your view, I can only demonstrate the problem with a different scenario, and then show how you fix it.

Take the following 2 simple tables:

CREATE TABLE dbo.Table1 (ID int IDENTITY,
                         SomeColumn varchar(20),
                         AnotherColumn date);
GO
CREATE TABLE dbo.Table2 (ID int IDENTITY,
                         T1ID int,
                         MyColumn varchar(20),
                         AnotherColumn datetime2(0));
GO

And now the following VIEW:

CREATE VIEW dbo.MyView AS
    
    SELECT T1.ID, --Intentionally qualified
           T1.SomeColumn,
           T2.MyColumn,
           AnotherColumn --Missing Qualification
    FROM dbo.Table1 T1
         JOIN dbo.Table2 T2 ON T1.ID = T2.T1ID;

When you try to create this VIEWyou get the following error:

Ambiguous column name 'AnotherColumn'.

This is because AnotherColumn appears in both Table1 and Table2; as a result SQL Server doesn't know what column you mean. Notice, however, that no complaints are given about ID as it's qualified with the alias of the table, T1, so SQL Server knows which table to get the value from.

So, to fix the problem, qualify the column with the name/alias for the object you want the data from. For example:

CREATE VIEW dbo.MyView AS
    
    SELECT T1.ID, --Intentionally qualified
           T1.SomeColumn,
           T2.MyColumn,
           T2.AnotherColumn --Qualified
    FROM dbo.Table1 T1
         JOIN dbo.Table2 T2 ON T1.ID = T2.T1ID;

And the error is now gone.

Upvotes: 2

Related Questions