locked
SSDT database project error: SQL71561 Unresolved reference to database objects(references to other database objects that are hard coded) RRS feed

  • Question

  • Hi,

    We are in the process of migrating 2005 dbs to 2012 and have created database projects in SSDT and have imported the databases

    Upon importing the projects fail to build due to numerous errors stating Unresolved reference to database objects(these objects refer to other database objects that are hard coded in the current project code)

    I have tried adding references(by generating a dacpac file through SQLPackage command line variable) and by adding the database reference(there are a lot of posts suggesting these)

    But none of these solutions resolve my reference issue.

    Even the below post was close to a solution but that dint work for me:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9188b21a-c864-4edd-81f3-8befb436c363/sql71561-sql71501-errors-containshas-an-unresolved-reference-to-object?forum=ssdt

    For example i get an error for this simple code for a project called Referring_Database

    Create view vw_view_name

    AS

    select column_name from Referred_Database.dbo.referred_table

    I have  added Referred_Database both through .dacpac file or explicitly as a database reference but still its showing the below error

    SQL71561: View: [dbo].[vw_view_name] has an unresolved reference to object [Referred_Database].[dbo].[referred_table]

    Even if we were to rewrite or remove the three part qualifier how will i go ahead as removing the database name will affect my view definition

    Is there any solution to resolve this reference issue?

    Thanks,

    Raja

    Wednesday, September 10, 2014 3:25 PM

All replies

  • Hello,

    I do think that reference to the three part qualifier/name is the issue. Using a two part qualifier you should not receive that error.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, September 10, 2014 3:41 PM
  • Hi Alberto,

    . We are in need to explicitly mention the db name(and 3 parts) in the query because its referring to another database 

    Thanks.

    Wednesday, September 10, 2014 3:44 PM
  • Hello,


    Are you referencing too many objects that reside on another database? Is it possible to import those objects and consolidate them on one database just for this migration or those objects have references too to other objects and is not possible?

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, September 10, 2014 3:54 PM
  • Hi,

    Yes, We have many objects that we refer to in the other database and we have imported those objects as well.

    Consider the below scenario. We need to migrate 

    db1
    db2
    db3
    db4..

    and we have created seperate projects with the database names as project names under one solution

    db1 has reference to db2
    db2 has reference to db3

    we have added corresponding database references/dacpac files(for db1, db2 was added as database reference and for db2, db3 was added as database reference)
    but still we are facing the reference issue.

    Thanks.

    Wednesday, September 10, 2014 5:53 PM
  • Hello,

    What about creating a .dbschema file and add it to the references? Have you tried that?


    http://sqlcodesnippets.com/2011/07/17/create-a-dbschema-file-for-db-project-reference/

    http://msdn.microsoft.com/en-us/library/aa833181(v=vs.100).aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, September 10, 2014 6:37 PM
  • Hi,

    we are using SSDT 2012(VS 2012) and i think we have to use .dacpac file as a database reference instead of dbschema file(.dbshema file is used in vs2008 and vs2010)

    Please refer below

    http://stackoverflow.com/questions/17193505/unresolved-reference-in-visual-studio-2012-database-project

    As already mentioned we have tried adding those references but still getting the same reference error.

    Any other workaround please?

    Thanks.
    Thursday, September 11, 2014 10:26 AM
  • Hi,

    we are using SSDT 2012(VS 2012) and i think we have to use .dacpac file as a database reference instead of dbschema file(.dbshema file is used in vs2008 and vs2010)

    Please refer below

    http://stackoverflow.com/questions/17193505/unresolved-reference-in-visual-studio-2012-database-project

    As already mentioned we have tried adding those references but still getting the same reference error.

    Any other workaround please?
    Hi,

    A possible solution is to add a database reference to the database that has the missing object.

    Refer to the link  for VS2012:
    http://www.codeproject.com/Articles/565459/FixingplusSSDTplusunresolvedplusreferenceplustoplu

    If it can't help you ,maybe you need to submit feedback to Microsoft at http://connect.microsoft.com/sql
    Saturday, September 13, 2014 2:31 PM
  • Hi Raja,

    Were you able to resolve this problem. How did you solve?

    Thanks

    AJ

    Wednesday, April 5, 2017 10:32 PM
  • It is now 2018 and this is still an issue.

    I have tried all the available "fixes" on VS2012, VS2015, VS2017, SSDT2012 and SSDT2015.

    Every fix states you need to have a database reference yet this does not solve the problem.

    Does anyone have an actual solution that works?

    Thursday, May 31, 2018 3:47 PM
  • was there a solution to this?
    Monday, August 27, 2018 12:39 PM
  • was there a solution to this?
    Monday, August 27, 2018 12:39 PM
  • I found out that the database projects should have the same Target Platform (project settings). When I set the platform to i.e. "SQL server 2016" then I should delete and create the database reference again.

    Hope this will help you or someone else :-)

    Wednesday, July 24, 2019 12:06 PM