26

Up until today, I was able to use the Schema Compare feature in Visual Studio 2012 to update a database from a database project. But now, for one project I can do the compare, but the update button is greyed out.

I am able to use other projects to update other database, but from this project I can't update any databases. I do not get any errors, the functionality is just unavailable.

Using publish still works. Also updating the project from the database works, just not the other way around.

Does anyone know why I wouldn't be able to update a database via Schema Compare?

Kris Harper
  • 5,672
  • 8
  • 51
  • 96
  • 2
    Possible duplicate of [Update target button is disabled after schema comparison](http://stackoverflow.com/questions/17742638/update-target-button-is-disabled-after-schema-comparison) – Michael Freidgeim Nov 30 '16 at 10:45
  • This was the answer for me: http://stackoverflow.com/a/32912849/441862 – Don Rolling Apr 07 '17 at 19:46

11 Answers11

45

Check the bottom of the screen after a compare it shows status messages there. I've seen this issue if there is a compile error in the database project. Once the error is resolved close and reopen the compare dialog. Rerun your compare and the Update button should be available again.

mark
  • 1,573
  • 13
  • 27
  • 2
    This is most likely the case if "update" button is not available. I resolved the errors and the button came up again. Thanks! – maulik13 Nov 05 '14 at 21:59
  • 2
    You sir deserve a cookie. This is not always visible (as sometimes the Error List window is hidden by another window/tab), just go ahead and check it. I wish VS warned the user about this, though... – tfrascaroli Apr 28 '16 at 15:20
  • also note, what is a compile error in one environment may not be in another. check your configuration to turn off errors. – Tom McDonald Jan 16 '23 at 15:19
8

You must check database users and database schema. Often, if database users aren't correctly replicated in DB Project, Schema Compare doesn't work.

Jean Noel
  • 947
  • 9
  • 7
  • In particular this can happen even if the database project is freshly imported from a database. Also, in my case I could hit update, it just wouldn't do anything. Behavior was the same in VS 2015 and 2017. – John Aug 29 '17 at 11:31
  • Once I added Schema it worked for me. No error message or any valid output in the output windows hinted that this will the issue. – Stagg Jun 03 '19 at 10:32
7

For me the error list pane and output pane weren't showing anything in Visual Studio 2015. Only after building the database project that I was targetting, was I able to see the errors in the output pane (but still not in the error list pane). After fixing these errors, the Update button was no longer greyed out.

MichaelvR
  • 494
  • 1
  • 5
  • 11
3

Ran into the same problem myself. As mentioned above, the normal Visual Studio Error List will list errors that block update... but further, there will also be warnings. One of the options that's enabled by default is that data loss blocks the update. That's the problem. Even though it's only a warning condition, any possible data-loss is functionally an error unless you change this flag.

imho, this is a pretty severe UI failure on MS's part, but what are you gonna do?

Pxtl
  • 880
  • 8
  • 18
2

What worked for me was including the schema. I was selecting to include only certain tables / procs etc. If the schema containing the tables and procedures is not also ticked, the import does not include the elements.

Jim
  • 14,952
  • 15
  • 80
  • 167
1

You need to ensure that all of your SQLCMD variables have default values.

Right-click onto the project within the Solution Explorer and select Properties.

On the tab to the left, go to SQLCMD Variables and enter the default value(s) into the column provided.

After running your schema compare another time, the update button should now be available.

Sonny Boy
  • 7,848
  • 18
  • 76
  • 104
0

In my case, the issue was that I had installed a newer version of SQL Server and SSMS (2016). You must always make sure you have the correct version of Sql Server Data Tools installed to match the version you are doing the compare against. Here is the link to SSDT for SQL Server 2005-2017 that I verified working with Visual Studio 2017:

https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

Mehdi Ibrahim
  • 2,434
  • 1
  • 13
  • 13
0

For me, I changed the order of the tables being added. If there is any relationship between any two tables, you have to add the parent table then dependent one to the database.

M Fuat
  • 1,330
  • 3
  • 12
  • 24
0

Not only does the schema have to be in your database project, it must be kept up to date if it changes on the database. Updates quit working after DBA granted a exec permission to a new sql login in the database on an schema that was in my project. After multiple failures for any stored procedure changes to get applied to my project, I updated the project, selecting only the schema that had changed. After updating the schema in the database project, the Update started working again. I include the schema in all updates. Hope this helps.

Ned Henry
  • 1
  • 2
0

Within Schema Compare go to Options -> General -> check *Ignore authorizer*

M.Z.
  • 71
  • 4
0

This issue usualy is thrown if there was a change of tables in one of the two Databases since you've clicked "compare". Regardless weather the change happens on a table that is beeing updated or not.

DerOta
  • 128
  • 7