Errors during build of SQL Server 2022 Projects with VS2019 Professional & AzureDataStudio

Question

Stuck with this issue for longer time.

I am trying to create SQL Server Projects for a DB being in SQLServer2022 using VS2019 Professional.

DB is old running/being in use since 2009.

How much ever I try I face the following warnings & errors, but of huge numbers

Build warning SQL71562: SqlProcedure: --- has an unresolved reference to object---

Build warning SQL71502: SqlProcedure: --- has an unresolved reference to object---

Build error SQL71501: SqlUser: [] has an unresolved reference to SqlLogin [].

Upon searching on google, I am unable to find any solution that would fix this issue.

Also, there isn't any proper documentation in MS (as far as I searched) where we could get proper explanation/guidance to reach out the solution.

1 thing to add, I am trying from both VS2019 and AzureDataStudio. But no luck from both.

But the AdventureWorks DB - I am able to create projects as well as able to build without any errors/issues.

Possible to help?

Answer 1

Hi @Radhai Krish ,

Thank you for your kindly feedback!

If the code that is failing is trying to use something in the “sys” schema or the “INFORMATION_SCHEMA” schema then you need to add a database reference to the master dacpac.

From this thread, we can get the method:

Add a database reference to master:

1. Under the project, right-click References.
2. Select Add database reference....
3. Select System database.
4. Ensure master is selected.
5. Press OK.

Note that it might take a while for VS to update.

Refer to: https://the.agilesql.club/2017/11/ssdt-how-to-fix-error-sql17502/

Kindly try if this suits to your requirement and let us know if further queries.

Thank you!

Answer 2

has an unresolved reference to object

You get that warning/error, when you reference an object, that do not longer exists, e.g. because it's deleted.

And you get it, when you have cross-database queries; is that the case here?

Answer 3

Hi @Radhai Krish ,

Thank you for your reaching out and welcome to Microsoft Q&A!

According to the message '71562' and '71502' shown in the issue, this is actually a warning and not an error. An error will stop the build process and prevent the project from being published to the server. Warnings merely show up in the "Error List" window, but don't prevent the build or publish processes from completing. Please refer to this official documentation if you don't want to see these warnings.

Regarding the error 71501, you could manage to solve it by first creating the login and then create the user.

-- Windows Account
    CREATE LOGIN [Domain\Username]
    FROM WINDOWS WITH DEFAULT_LANGUAGE = [us_english];

    GO

    CREATE USER [Domain\Username] FOR LOGIN [Domain\Username];
    GO

    -- Sql Acccount

    CREATE LOGIN [sql_account] WITH PASSWORD = 'Ch@ngeth1spA$swurD'
    GO

    CREATE USER [sql_account]
    FROM LOGIN [sql_account]
    WITH DEFAULT_SCHEMA = dbo

    GO


    -- Then set the sql file Build Action to "Build"

Feel free to share your issues here if you have any concerns.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 4

Hi @Radhai Krish ,

Thank you for your kindly feedback!

Should we need to create logins to all the users?

No, we don't need to create logins to all the users.

Here is a known issue like yours, please refer to this article.

In addition, if you are getting Error SQL71501 while creating Dacpac from SQL Server database.

Please refer to the post here https://www.red-gate.com/simple-talk/blogs/extracting-dacpac-when-sql-server-refuses/ where you may skip validation step and could proceed.

Kindly try if this suits to your requirement and let us know if further queries.

Thank you!