Database project items precedence

0
votes

In Visual Studio 2013 Pro I have created a database project used to manage database schema changes.

In this project, I have added two items:

1. a synonym DDL script, which refers to another database:

CREATE SYNONYM [dbo].[synMerchCats]
    FOR spaceman_sys.[dbo].USR_CAT_CATEGORY

2. a view DDL scripts, which refers to the synonym script from the point 1.

CREATE VIEW [dbo].[vwMerchCats]
AS Select
    CODE,
    NAME
From
    [dbo].[synMerchCats]

When I try to build the project, it throws out the error:

Error 8 SQL71501: View: [dbo].[vwMerchCats] has an unresolved reference to object [dbo].[synMerchCats].[NAME]. Database c:\users\zienka01\documents\visual studio 2013\Projects\AsoBulkFiller\Database\Views\vwMerchCats.sql 4 3

I understand that view could not recognize the synonym created in the previous step, however I do not know why? Are there any limitations coming from the fact, that I'm crossing objects sourced in two different databases?

Can you advice how to structure this project to make it running?

Thank you.

1
visual-studio-2013database-project

1 Answers

0
votes

You can add a database reference and prefix the object with the reference name;

CREATE VIEW [dbo].[vwMerchCats] AS Select CODE ,NAME From [$(ReferenceName)].[dbo].[synMerchCats]

and it should resolve the issue.