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:

• checked if the columns actually exist in one of the source views
• aliasing the source views (now all columns throw a build error)
• not using the [$(DatabaseName)].dbo.View... reference, just dbo.View_... (no change)
• checked if DB2 has been built to produce a dacpac
• checking if the SQL Server version is the same for DB1 and DB2
• checking that I have built DB2 in the same Build mode (Debug/Release) as I have tried to build DB1
• writing View_source_2.Some_column instead of Some_column

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.

CodePudding user response：

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.

CodePudding user response：

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.