none
SSDT 2017 Database project has an unresolved reference to object RRS feed

  • Question

  • I need help trying to resolve errors in database project. 

    Background:

    We are evaluating using database projects when developing SSIS packages. We have many SSIS packages that each have database objects in a common database. Each database project would only have DDL objects that pertain to the interface they are developing on. 

    So there are some common objects like Schema and Synonyms that may be in the database that they would need to reference. 

    I created a DACPAC that would have these objects and created a reference to them. Most things work as expected. However, the synonyms  are giving me trouble. 

    I have a synonym in a common database that is created using a object in another database. Example: 

    CREATE SYNONYM [qa].[syn_northwind_tbl_customerdemo] FOR [Northwind].[dbo].[CustomerCustomerDemo]

    This is part of the dacpac that I have referenced in my project,

    If I try to create a view from that synonym, I get a unsolved reference. 

    Example: 

    CREATE VIEW [qa].[view_syn_northwind_tbl_customerdemo]
    AS SELECT * FROM [qa].[syn_northwind_tbl_customerdemo] 

    SQL71501: View: [qa].[view_syn_northwind_tbl_customerdemo] has an unresolved reference to object [qa].[syn_northwind_tbl_customerdemo].

    I can not find any info how to resolve this reference. I can not build the project or deploy because of the error. 

    Is there any way to resolve or to suppress the error so it would build and deploy?

    The Suppress Reference Warnings to True does not help in this case. 

    I have also added a Northwind dacpac to see if that was an issue and has not helped.

    Also, If I change the view to a stored procedure in the project the unresolved reference goes away. 

    Example: 

    CREATE PROCEDURE [qa].[proc_syn_northwind_tbl_customerdemo]

    AS
    SELECT * FROM qa.[syn_northwind_tbl_customerdemo]
    RETURN 0

    Thanks

     

    Harold Buckner



    • Edited by Harold Monday, January 7, 2019 7:54 PM
    Monday, January 7, 2019 6:43 PM

All replies

  • CREATE SYNONYM [qa].[syn_northwind_tbl_customerdemo] FOR [Northwind].[dbo].[CustomerCustomerDemo]

    Hello Harold,

    You get the "unreferenced" error because in the synonym definition you point to a different database (or is it the same DB?).

    You have to add that DB as a reference, see Add Database Reference Dialog Box


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, January 8, 2019 7:33 AM
  • The synonym points to a different database and the synonym is part of the dacpac that I have referenced in the project. 

    Only views created with the synonyms give the error. Stored Procs using synonyms do not error. 

    Harold Buckner


    • Edited by Harold Tuesday, January 8, 2019 1:00 PM
    Tuesday, January 8, 2019 12:54 PM