locked
Visual Studio SQL Database Project Reference Issue RRS feed

  • Question

  • Dear All,

    I am trying to create two separate visual studio solutions for the same SQL Azure Database.

    Solution files have two separate SQL DB Projects pointing to separate Azure Git Repos but same SQL Database.

    when I try to create procedure or view referencing the tables created in the other project I am getting error : 

     

    Severity Code Description Project File Line Suppression State
    Error SQL71501: View: [dbo].[test] has an unresolved reference to object [dbo].[test_pop]. C:\Users\a\b\repos\sqldb\pqr\xyz.sql 4

    Is there any way we can refer other DB schema in the first DB ?

    Also, this will be part of Azure DevOps so it should work in the Build and release pipelines to deploy the SQL code.

    Thanks 

    Wednesday, April 1, 2020 7:26 PM

All replies

  • Hi Razoramon,

    Try running a schema comparison from the DB to your project and see if it suggests creating or making changes to any schema. Also make sure that the schema file is included in the build (Properties -> Build Action = 'Build').

    More information on how to accomplish this:

    How to: Use Schema Compare to Compare Different Database Definitions

    Please let me know if you have any additional questions or need assistance.

    Regards,

    Mike

    Thursday, April 2, 2020 10:40 PM
  • My Question is more towards security that developer 1 using VS SQL DB Project 1 should not see schema of other VS SQL DB Project 2 used by Developer 2 but they have access to Schema reference so that they can use the tables of other schema in their schema stored procedures.

    these two VS SQL Project can be in two separate Azure repos.

    Thanks

    Friday, April 3, 2020 2:10 PM
  • Thank you for this clarification. I am going to move this thread to the Visual Studio Integrate forum, as the community audience is better suited to answer this question.

    Regards,

    Mike

    Friday, April 3, 2020 11:43 PM
  • I don't think what you want is possible, but I want to make sure I'm understanding what your project structure is.

    Here's a simplified version of the structure as I understand it, please let me know if I am wrong.

    Actual Database:

    DB Project 1:

    DB Project 2:

    The goal: Allow devs to create views and stored procs that use any of the 3 tables even though there are only 2 in their copy of the DB project.

    Your devs for project 1 are trying to create the following view:

    CREATE VIEW [dbo].[View1]
      AS SELECT * FROM [Table1] a
        INNER JOIN [Table2] b
        ON a.id = b.id


    The main problem with using a pipeline that uses database projects built into DACPAC files this way is that when you do the deployment, it will try to update the database schema to match the one in the file exactly. That includes deleting tables not in the project. The default settings will cause the deployment to fail by default due to the risk of data loss. The build error is occurring because the if you were to deploy project 1, then table 2 wouldn't exist for the view to reference.

    If you can't have your devs share a DB project that contains all the tables using update scripts may be an option. Keep in mind that a DB project isn't the same as giving access to the database- the devs aren't getting access to any data, just the schema. It's an extra step for your developers, but ADO is capable of doing deployment based on a script instead of a DACPAC file. They could create a script that has just the changes they need that is run in your build process.

    Using schema compare between the project and the database can make this a lot easier. As long as the target is a database (not a DB project) it can generate a script that does all the changes. Schema compare doesn't validate the scripts, so you could use it even if the devs didn't have the ability to see all the tables in the database.

    Wednesday, April 15, 2020 4:02 AM