question

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 asked Criszhan-msft answered

Visual Studio Data Tools - source control and buld errors around aliases SQL71561

Hi,

I would like to start using VSDT for database development for an existing set of databases, I will also like to develop on the databases and for it to be in source control automatically. Here are my current blockers and questions.

  • As I have multiple databases, should I create one project per database. The nature of my work is that I can use any of the databases.

  • What is the best way to add the database objects into source control, create the project and then add this to source control ?

Lastly, I am experiencing the following errors during the build process after importing one of the databases.

SQL71561: Computed Column xxxx contains and unresolved reference to an object. Either the code object does not exist of the reference is ambiguous because it could refer to any of the following objects [LINKEDSERVER].[DATABASE].[dbo].[object_name] or [LINKEDSERVER].[DATABASE].[dbo]::[object_name]


Is the only solution here to export the entire linked server as a DACPAC file and add it as a reference, what if I do not have access directly to the remote linked server instance or if its not a SQL server database instance ?





 create view [schema].[test]
 as
 select distinct
     [ai].[account_id] as [service_account]
 from
     [linkedserver].[database].[dbo].[view_name] ai
 where
     stage = 'start'
 and
     srn not in (
     select distinct 
         [ai].[account_id]
     from 
         [linkedserver].[database].[dbo].[view_name] ai
     where stage = 'progressing'
     )

I get the error

SQL71561: Computed column [schema].[test].[service_account] contains and unresolved reference to an object. Either the object does not exist or the reference is ambigious becuase it could refer to any of the following objects. [linkedserver].[database].[dbo].[view_name].[account_id] or [linkedserver].[database].[dbo].[view_name].[ai]::[account_id]

sql-server-general
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Is the only solution here to export the entire linked server as a DACPAC file and add it as a reference, what if I do not have access directly to the remote linked server instance or if its not a SQL server database instance ?

In order to query using a linked server in a database project, you need to add a database reference to your project which requires that you create a DACPAC file. For the situation you mentioned, I will try to research if there are other ways.

0 Votes 0 ·

As I have multiple databases, should I create one project per database. The nature of my work is that I can use any of the databases.

Those databases are on the same server?

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered MrFlinstone-1451 commented

Cross database and linked server are difficult to handle in a database project.

One option: In you database project create the view as a dummy, like

 create view [schema].[test]
  as
  select CONVERT(varchar(50), '') AS [service_account]

and in the post-deployment you add a "ALTER VIEW ..." command with the real query against the linked server.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the proposed answer, I am thinking is the issue due to the linked server or the alias, reading the error message again points to alias, it could end up being because of the linked server but I will try out this option.

0 Votes 0 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.