4

I can successfully build my DB project in the Azure data studio with the SQL DB Projects extension. (not with SSDT but similar to this: https://www.sqlshack.com/two-ways-to-build-sql-database-projects-in-azure-data-studio/ When I right-click on the DB project and select "Update Project from database", it lists the delta between the online Azure SQL DB and the local db project.

The problem now is that when I either click on "Generate script" or on "apply", I receive an error.

Generate script: Performing script generation is not possible for this comparison result.
Performing script generation is not possible for this comparison result.

Apply schema compare changes: Object reference not set to an instance of an object.
Object reference not set to an instance of an object.

I already reinstalled the Azure Data Studio but with no success. Any hint what I could do to fix this?

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
Chris
  • 143
  • 7
  • I am also receiving this error using v1.16.0 of the schema compare extension and v0.19.0 of the database projects extension. I received this error on previous extension versions when I tried to apply a subset of changes picked up in the schema compare but not others. Applying all changes "resolved" the error in that instance, but this time I have no such luck. Although one difference is that I have an add sqluser change which schema compare is forcing me to apply (can't uncheck) because of a dependency on sqlrole change so perhaps there is something there? – Nick Sep 22 '22 at 15:01

1 Answers1

2

In our case these errors occur because it is added a Role Member to the Standard MSSQL Database Roles called db_datareader and db_datawriter. These roles (or objects as ADS calls them) do not exist in the local dacpac or ADS Database Project. It seems to map even if the roles are checked for update or not, so the Schema compare can't find the objects locally.

If the same applies to your project I will suggest checking if that user really needs to be a part of those two roles as they are not being mapped locally. If you need the member in the role, a workaround is to exclude the Database Roles objects in the Schema Compare:

  1. In Schema Compare, press Options.
  2. In modal on the right, press the Include Object Types-tab
  3. Find Database Roles and untick.
  4. Press OK and re-compare.

I've set up an issue to the ADS-team, so hopefully the Schema Compare- and Database Project-responsibles can cook up a good solution on this problem.

Martin
  • 21
  • 1
  • 4
  • As of the issue linked in my answer this is due to be released in the December -22 release of Azure Data Studio. – Martin Nov 04 '22 at 22:55
  • Thank you, your response put me on the right path to make the script generate in my case which was for SQL Server on premise (not Azure). I had to uncheck the following in the "Object Types" tab of the "Schema Compare Options" modal: `Database Roles`, `Permissions`, `Role Memberships` and `Users`. After re-comparing and hitting the Generate Script button, I was greeted with a green tick instead of a "Failed" red X. – liviriniu Jul 02 '23 at 16:26
  • That’s great @liviriniu! I guess what you have to untick depends on what built in SQL Server-commands you have modified locally. I would have hoped the ADS team would have fixed the issue by now, but maybe not – Martin Jul 03 '23 at 17:20