Schema Compare Improvements

re: Schema Compare Improvements

Greg — Tue, 08 Jan 2013 15:59:34 GMT

What about the ability to filter out objects by name? My example is I have a database which dynamically generates individual stored procedures for applications to use (not to go into too much detail but basically users can pick a set of filters which get turned into a stored procedure, I know I know we are working on making the filters table driven, but it is what it is at the moment). So we have shifting list of 4000+ stored procedures that are only in our production environment. When I compare between our dev and prod environments I want to be able to completely ignore those stored procedures.

re: Schema Compare Improvements

Bill Gibson MSFT — Wed, 19 Dec 2012 01:14:47 GMT

Hi Scott, sorry to hear you've been having problems with Schema Compare. I have not been able to repro either problem using the current (December 2012) release of SSDT based on your description with databases I have tried. The first problem sounds like a dependency analysis issue - there are some known issues with this code, but your scenario is not normally a problem. I can exclude all Add objects on a database that I'm looking at. Would it be possible for you to send a copy of your database schema(s) and projects and detailed repro steps for both problems . If so, could you create a dacpac using the latest version of SSDT and verify that a database created from the dacpac exhibits the same problem. If it does, could you send the dacpac file to me at bill dot gibson at Microsoft dot com.

In future, I recommend using our forum to post questions or report issues like these. The forum is actively followed by the team and the community, whereas comments on blogs, especially old posts like this, might get overlooked. The forum is located here: social.msdn.microsoft.com/.../threads

Again, apologies for the inconvenience, and thanks for reporting the issue.

Regards,

Bill

re: Schema Compare Improvements

ScottYan — Tue, 18 Dec 2012 03:41:20 GMT

I used sql schema compare for several days, and it just make me CRAZY!!

The first problem is the checkbox (of "Action" column) can't be unchecked for no reason. I dig this for several hours, I tried settings , and searched at internet, but nothing helps. To simply re-create this issue, I created a new database in my localdb, and set it to "target", then choose an existing sql server database as "source", after compare, I exclude everything of "Add" folder, and ....most tables, users(which I don't need), schemas, are still be checked, and the checkbox is gray, so, why it compel me creating those tables? I think a tool should never treat users as kids.

The second problem is , after some compare and updates, the localdb database(target) is all the same with source, except missing column description(extended properties), the compare will found the difference, and even I can see sql code in "Object Definitions" panel , but , when I click update, nothing will happen, if I compare again, the difference is still there, I checked the scripts it created, just...nothing. The scripts reported "update successful", but in fact it simply ignored the extended properties, again, I tried settings, searched at internet for hours , nothing helps.

so, what's wrong ?

re: Schema Compare Improvements

Vairam — Tue, 06 Nov 2012 01:34:43 GMT

Until Data Compare not available for SQL Server 2012, Business Intelligence Tools and SSDT tools not available in VS 2012 shell, these don't buy anything to migrate to SQL Server 2012.

re: Schema Compare Improvements

Bill Gibson MSFT — Tue, 24 Apr 2012 06:55:30 GMT

Apologies for missing your comments!

Regarding your first question, .dacpac files are replacement to the .dbschema files created by vsdbcmd.exe. You can create .dacpac files using SSMS (the wizard referred to in your next question), SSDT, or SQLPackage.exe which replaces vsdbcmd.exe. SQLPackage.exe is installed with SSDT and located by default on a 64 bit machine here: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin. If you want to install SQLPackage stand-alone you will need to install DACFx plus its dependencies. These are available as part of the Microsoft SQL Server 2012 Feature Pack, downloadable here: www.microsoft.com/.../details.aspx.

Regarding your second question, when extracting a dacpac, it contains the normalized code stored by the database engine, which may be formated differently than the code in your project. Schema Compare will not normally report format differences which are not semantically significant. In some case (for example script of a computed column) where the semantics cannot be reliably determined it may report a difference. In this case you can update your project to match the code stored in the database using Schema Compare. If this is not consistent with what you're seeing then let us know.

In future, I recommend using our forum to post questions like these. The forum is actively followed by the team and the community. The forum is located here: social.msdn.microsoft.com/.../threads.

Regards,

Bill Gibson

re: Schema Compare Improvements

Jason — Thu, 12 Apr 2012 16:18:41 GMT

This is absolutely terrible. The "Extract Data Tier Application Wizard" changes the syntax and formatting of objects. This makes comparison with my database project just about impossible. The previous VSDBCMD method that created a dbschema file did not have this behavior. Why can't you support comparing dbschema files any longer? I'm starting to regret investing my time and money into a Vistual Studio database project. I really thought that I could make it work.

re: Schema Compare Improvements

Jason — Thu, 05 Apr 2012 21:23:23 GMT

We can't compare .dbschema files anymore??? So how am I supposed to do a schema compare of the remote SQL 2005 databases that I have to support? Up till now, I've been using the VSDBCMD to get the remote schema off the remote SQL 2005 database and then compare the .dbschema file with my local project.

What can I install on the remote SQL 2005 server that will allow me to generate some kind of file I can use to compare the schemas?