50

I have created database project using VS 2012. Once the schema comparison is done, the update target button should be enabled to sync with target, but it's not getting enabled. How can I fix this?

I'm using VS 2012 with SP 3, SQL Server 2012,

enter image description here

Jeff Schaller
  • 2,352
  • 5
  • 23
  • 38
Aruna
  • 1,962
  • 4
  • 25
  • 50
  • I've created VS UserVoice suggestion [Keep "update" and "generate update script" buttons enabled](https://visualstudio.uservoice.com/forums/121579-visual-studio-ide/suggestions/17396128-keep-update-and-generate-update-script-buttons). Please vote if you agree. – Michael Freidgeim Dec 11 '16 at 07:26
  • Happens in other versions of VS as well. I'm seeing this in VS2017. – pcdev Jul 12 '18 at 07:05

12 Answers12

91

Check the Error List if you have any error, I had a non recognized word in VS (but it did in SQL Server), I commented it out, re-compared and it was enabled successfully.

Francisco G
  • 1,434
  • 2
  • 14
  • 22
  • 6
    This was my problem, always remember to check the Error List window! :) Thanks @FranciscoG – kzfabi Feb 03 '15 at 13:16
  • 1
    make sure the target version in the project settings is for the correct version of SQL Server, then there should not be unrecognized words. – cdonner Aug 31 '16 at 18:29
  • 5
    NB: It seems that **any errors** prevent the UPDATE button from showing; even if they're on objects which you've excluded from the update. – JohnLBevan Oct 28 '16 at 13:31
  • Thank you. I failed to check the Error List window. When I did I found an error, resolved it, re-compared, and the button is no longer disabled. – SherlockSpreadsheets Aug 14 '18 at 20:53
  • I had no errors in the error list in VS 2017, the only error I could see was in the Output tab – Matthew Nov 22 '18 at 03:58
  • 1
    @Matthew probably you have to update the SQL Server Data tools for VS. https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017 – Francisco G Nov 23 '18 at 11:49
  • In my case I had no Errors, but 3 Warnings. Fixing those did the trick. – codah Dec 10 '20 at 01:49
15

See if you have a "warning" message stating "Cannot generate deployment plan due to an internal error". If so, the Update and Generate Script buttons will be disabled.

Close Visual Studio, navigate to the folder containing the Database project and remove all (*.dbmdl) files. Then restart Visual Studio, re-run the compare schema at which point the Update button should be enabled.

johnl
  • 234
  • 3
  • 6
  • This shouldn't be the correct answer, but it is. Thank you! – Don Rolling Apr 07 '17 at 19:45
  • That warning is also present when there are external data sources or tables that can't be updated, even if they are excluded. My workaround is to create a new temporary project, update everything except the externals to it, then compare it to the database. – 8forty Jan 23 '20 at 20:04
4

Workaround:

In my case. Fixing Error was not priority task. Also i was not able to find any Error in Error List.

Its just you dont have to use your "Visual Studio Database Project" in source or target. Instead of using project, create a temp DataBase using script already with you.

Select this (or these) temp database(s) in source and (or) other in target.

Button must be Enabled.

For me getting difference was far more important than fixing the issue. Hope it helps you. With some more improvisation.

Prashant P
  • 51
  • 2
  • 1
    This helpful answer could be worded better but it does work when you have references in your database to other databases and your errors are being generated because of that. What Prashant P is saying is instead of comparing a database on a server to a database project in your solution you can use the database project in the solution to create a database (I create it on my local sql server instance) and then compare the two databases. – user2721607 Aug 02 '17 at 16:20
  • Yes but in this case, if all you really need is the comparison, you don't really need the "Update" Button to be active. – KarmaEDV Nov 06 '17 at 13:40
  • @KarmaEDV true, but that part's a bit academic because the answer still helps if you do need Update. Also with an error like that, who knows whether the comparison can be relied on. In my case I did need Update, and by restoring a "reasonable" backup to my local server and running the compare between the two dbs first, I managed to bypass the Schema Compare bug and get the Update button enabled. And after applying that update, the issue was resolved and I could compare and update from the project as originally intended. Result! – Reg Edit Jul 13 '19 at 20:56
3

It seems your database project has sqlcmd variables without default values.

See: http://www.andrewburrow.net/vs2012-schema-compare-buttons-disabled/

Hope this helps.

Stanislav
  • 576
  • 3
  • 11
2

I couldn't see any errors in Error List, but I could see the errors in the Output tab

enter image description here

Matthew
  • 1,630
  • 1
  • 14
  • 19
0

the everytime you open the "options" on "schema comparison" you must click on "compare" button again to activate the "update" button. However if it doesn't work at the first time, just close and reopen the Schema comparison file again.

Alex Vieira
  • 1
0

For me, there were no errors visible in the Error List. This was because I had the "Show Issues Generated" drop down was set to "Build Only"; changing it to "Build + IntelliSense" allowed me to see the SQL errors that needed to be fixed to enable the Generate Script and Update buttons.

Jordan Marr
  • 107
  • 3
0

In SSDT I had the case where the update button was enabled but the generate script was not enabled. This was because my destination schema was my local solution (the *.sqlproj vs project).

It took a few minutes to dawn on me that the destination needed to be a real database to correctly generate the script.

Nick Rubino
  • 965
  • 7
  • 9
0

And after all, when you have the available Update Target button and the disabled Generate Script button.

See if your source is a database and your target is your SSDT project.

If so then click "Switch source and target" button.

The Generate Script button will become available.
I.e. a SSDT project must be the source and a database must be the target in the Schema Compare window.

it3xl
  • 2,372
  • 27
  • 37
0

Workaround 2:

In my case, the Update button was greyed out because of there were errors warnings about a view that I created and added to the project earlier. The problem was that the view was scripted to use full database qualified object naming. E.g. [databasename].[schemaname].[objectname], which works fine in SSMS, but causes a problem when it is checked in a DB Project. If you use three part naming for referring an object in same DB project results in a error/warning in Visual Studio.

By changing the code to [schemaname].[objectname] in the definicion of the view removed the error warnings and enable the Update option. Happy days!

Credits: https://visualstudio.uservoice.com/forums/121579-visual-studio-ide/suggestions/3577074-allow-three-part-naming-for-referring-a-db-object

https://visualstudio.uservoice.com/forums/121579-visual-studio-ide/suggestions/17396128-keep-update-and-generate-update-script-buttons

Vlad
  • 1
0

Typically the update button is grayed out when the database objects are available in the separate sql files physically inside the database project but are not included in the database project itself. I simply added those sql scripts into the project and the update button got enabled after comparing the schema.

Munaf Khatri
  • 1
0

In my case, the Update button was disabled because there was a scalar function that was modified and that function was used in a computed column definition.

My solution was to:

  1. Manually drop the computed column(s) in the database
    • ALTER TABLE <your table> DROP COLUMN <your computed column>
  2. Re-run the schema compare
  3. The Update button was enabled, and the update completed successfully
William Herrmann
  • 343
  • 2
  • 10