0

I'm converting an existing DB into SSDT project and getting a ton of these errors, despite having added the reference to master DB. They can't be ignored because they're errors, not warnings, so it won't build.

Error SQL71501: View: someschema.someview has an unresolved reference to object [sys].[dm_hadr_fabric_continuous_copy_status].

Error SQL71561: View: someschema.someview has an unresolved reference to object [master].[sys].[server_resource_stats].[start_time].

It looks like master DB is outdated and doesn't recognise several views in Azure SQL Managed Instance. Is there any way to get afix this or get an updated master dacpac so I can build this project?

Jane Dilbert
  • 13
  • 9
  • Did you manage to solve this? We have both SQL71501 and SQL80001. The latter isn't solvable unless MS updates the linter to recognize correct sql used in Synapse. It erroneously detects problems with create table statements that is accepted by the Synapse dedicated sql pool. – Molotch Apr 21 '22 at 08:17
  • Haven't found a way to solve this. MS needs to update it for the references to work. – Jane Dilbert Apr 23 '22 at 10:50

1 Answers1

1

To resolve the SQL71561 build error please follow:

Add a reference to the other database, clear out the "Database variable" field in the dialog. If you don't clear this field, when doing a schema compare, SSDT would generate the script using the database variable name, which would fail.

  1. Add a Database Reference to the project.
  2. If in case the other database was another project in the same solution, you would be able to select it in the first drop down on the "Add Database Reference" dialog.
  3. Make sure the text in the "Database name" field is correct.
  4. Clear out the "Database variable" field.

Look at the "Example usage" text and verify that it looks as expected. Click "OK" to add the reference and that should take care of the 'unresolved reference' errors.

After doing this, you will be able to do a schema comparison, but could get the following error:

Error 408 SQL00208: Invalid object name 'db.schema.table'.

Go to the project properties and uncheck "Enable extended Transact-SQL verification for common objects" will allow the project to build successfully.

Consider and follow the below steps when converting existing DB to SSDT:

  1. Create a new SSDT database project

  2. Import the existing DB into that project, which will recreate all the script files and change the way they are organized/grouped

  3. Do some cleanup on a few of the script files, including the post-deploy script

  4. If it is SQLCLR project, make sure master DB stuff has done right (creating logins)

    4.1 Make sure SQLCLR is enabled and configured correctly in the SSDT project's SQLCLR property page

    4.2 Copy the source code for the SQL CLR into the top level of the project
    4.3 Add the references need by the SQL CLR code

  5. Compile

  6. Publish

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14
  • That answer doesn't apply. As stated: "Getting a ton of these errors, despite having added the reference to master DB. It looks like master DB is outdated and doesn't recognise several views in Azure SQL Managed Instance." – Jane Dilbert Aug 13 '21 at 08:13