6

I am attempting to compare schemas of two databases on the same server.

The two databases are named benchmarkdb and benchmarkdb_dev.

In Visual Studio 2017, I go to Tools > SQL Server > New Schema Compare...

From both drop down menus labeled "Select Source" and "Select Target" (as seen in the picture below), I want to select benchmarkdb as source and bencmarkdb_dev as target.

enter image description here

After clicking to select a source, the "Select Source Schema" pops up, and I clicked the "Select Connection..." button, which brought another popup. The newest popup (the one on the right in the picture below) shows both databases I am trying to compare.

enter image description here

However, after I choose one for either the source or the target, all pop ups close, but nothing populates the "Select Source..." or "Select Target..." drop down menus. In fact, it just goes back to looking like the first picture.

Is there a solution to this so I can compare the schemas between the databases?

Update:

I have tried this on VS 2012, 2015, and 2017. All are facing the same issue. VS software and system software are up to date. Below are some versions of software that I am using, if it is any help.

Windows 7 Enterprise SP1

VS 2012, 2015, and 2017

SQL Server 2014

.NET Framework v4.7.02053

ccbadger
  • 123
  • 1
  • 1
  • 7
  • Have you tried after running visual studio as administrator ? – Vikrant More Jun 07 '17 at 18:45
  • 1
    Would not recommend using VS 2017 for anything SQL. A number of tools and addins are still missing or only partially functional including SSDT. – Jacob H Jun 07 '17 at 18:58
  • @VikrantMore Yes I have tried running VS as admin. I am still faced by the same problem. – ccbadger Jun 07 '17 at 19:13
  • @JacobH I was unaware of this. Is 2017 the only version with these afflictions? Or are there other versions I should stay away from also? – ccbadger Jun 07 '17 at 19:17
  • VS 2015 seems to have everything. I am not saying this is the cause of your issue, but it's certainly been a huge roadblock for me going to VS 2017. – Jacob H Jun 07 '17 at 19:18
  • @JacobH I don't know if it is the cause of my issue either, but it's worth a shot. I will try VS 2015 and post back with the results. Thanks for your help. – ccbadger Jun 07 '17 at 19:46
  • @JacobH After trying this with VS 2015, I have encountered the same problem. If the partially functioning SSDT was a cause of the problem, it is not the only cause. – ccbadger Jun 07 '17 at 21:36
  • The latest SSDT updates target VS 2015 *only*, with changes that mainly target SQL Server 2017. VS 2017 is left with the RTM tools. VS 2017 updates are supposed to come out sometime this summer. – Panagiotis Kanavos Jun 08 '17 at 15:34
  • For example, there is no SSIS support at all. SSRS and SSAS support is available separately through "Preview" packages. Best not to use them for developmen/production work – Panagiotis Kanavos Jun 08 '17 at 15:37
  • @PanagiotisKanavos Thanks for the info! I did try VS 2015 Professional v14.025431.01 Update 3 (latest version at time of writing). Unfortunately, it struggled with the same problem as VS 2017. – ccbadger Jun 08 '17 at 17:45
  • I am encountering the same issue: – Mike Aug 14 '17 at 20:26

3 Answers3

7

OK, so I managed to fix it. First off, you need to install SSDT from Microsoft pages. Just google it. Make sure that you also have it listed in individual components when starting Installer (on Modify). Only SQL Server Database is required (I could choose from Integration Services and 3 more).

Next, you need to "Remove connection from history" in Schema Compare modal for connection selection.

I closed VS at this point, to make sure that the connection is not in history anymore. Starting VS the next time, you should not have the problematic connection in history.

Recreate the connection (I checked "remember password") and voila. Schema compare works.

Not really sure, but in desperation I also installed "Redgate ReadyRoll Core", "Redgate SQL Prompt Core" and "Redgate SQL Search". I don't think it matters, but if all else fails...

Edit: This worked on 2 machines and it doesn't seem to be connected to VS version.

Cubelaster
  • 338
  • 4
  • 6
2

I had the same issue, but I don't know if I had the same root cause.

My problems started after changing my database from SQL Server on a Window Server to SQL Server on a Linux Container. The schema compare would appear to accept my selection but the database field would never actually populate and I could not perform a comparison.

There were 2 things I had to do to solve the problem.

  1. Update Visual Studio (using the standard updater within Visual Studio).
  2. Update the SQL Server Development Tools (SSDT) add-on for Visual Studio to a preview version that supports SQL Server on Linux. That download is available here.

After both updates were in place I could select the database and execute Schema Comparisons between my project and SQL Server on Linux exactly as I had been doing for SQL Server on Windows.

Andrew Pirkola
  • 21
  • 4
0

If running a local database, check what version of SQL Management studio you are using

I had the same issue running Visual Studio Pro 2017 and SQL management studio v18.6 with SQL Server 2017 installed. I tried all of the above solutions and no luck Even going back to Visual Studio 2015 didn't work for me. What did work though was changing back to SQL Management Studio v17.9.1

No issues any more. I may try to upgrade Visual Studio to 2017 again to see if that works now

cormacio100
  • 67
  • 8