locked
When are Schema Compare objects really equal? RRS feed

  • Question

  • Here is my scenario:
    1.  I created a database project and deployed the database to pc1.  I don't know if this matters or not, but the original database project imported all of the objects from five smaller database projects into one database.  After I had the combined database on pc1, I removed the five smaller projects from the solution.
    2.  I deployed the same database to pc2.
    3.  I made a change to an object (e.g. stored procedure) on pc2.
    4.  I did a schema compare between the database on pc2 and the project, and the results displayed the change.  I did Write Updates to get the change into the project.
    5.  I refreshed the schema compare and no differences were displayed.
    6.  I did a schema compare between the project and the database on pc1 and the results displayed the difference.  I did Write Updates to get the change from the project into the database on pc1.
    7.  I refreshed the schema compare and no differences were displayed.
     
    However, when I ran a schema compare between the database on pc1 and the database on pc2, they are not the same.  What is actually happening with the compares and updates?  If I then Write Updates and compare again, finally both databases are the same.  How do I really know when they are the same, and/or what should I actually be comparing to tell if there are differences or not?
     
    I repeated the original process by comparing the project to the database on pc2 and reversed to compare the database to the project after writing the updates, and also both ways on pc1.  All four of the comparisons do not show any differences.  However, when I compare the two databases, it still shows that the databases are not the same until an update is done between the two databases.
    Friday, February 8, 2008 8:06 PM

Answers

  • drop & recreate them?

    it's a shame the schema compare tool doesn't have different types of "different" marking -- since there's 'Different - but if you run a build I won't change anything' and there's 'Different - and I will force this to be changed if you run a build'
    • Marked as answer by StevenPo Tuesday, November 11, 2008 9:36 PM
    Wednesday, March 19, 2008 3:10 PM

All replies

  • Which objects show as different when you compare the two databases?

     

    If you are comparing a project with a database you are comparing only the objects represent as Schema Objects, not objects that are in pre and or post deployment scripts.

     

    -GertD

     

    Monday, February 18, 2008 1:52 AM
  • I am only getting started with the tool, so I haven't even tried any pre or post deployment scripts yet.  I have only tried standard Schema Objects such as tables, views, stored procedures, and triggers.  I have seen this behavior more than once.  Now that I have installed the 2008 Power Tools, I can try it in 2008 and see if the same thing happens.  I could also try it by starting with a change in the originally imported database and pushing to the deployed database and doing a Schema Compare.
     
    Monday, February 18, 2008 2:52 PM
  • One common reason why objects are different is caused by the fact that the SET options used to create the objects are different.

     

    Besides that there are a couple of options for comparing databases in Tools->Options->Database Tools->Schema Compare that you can select.

     

    For example when using unnamed constraints those are automaticly named by the system and can cause a comparison difference. Or an other common one is that SQL Server adds extra parenthesis around check constraints which can cause comparison differences.

     

    -GertD
    Tuesday, February 19, 2008 12:56 AM
  •  

    Randy -

     

    Are you still seeing this compare issue, or has it been resolved? I wanted to make sure that we didn't leave you hanging.

     

    thanks,

     

    Thursday, March 6, 2008 5:03 PM
  • I'm currently using Microsoft Visual Studio Team System 2008 Database Edition and I am still having issues with the comparison reporting all tables with constraints as different because of the double parentheses.

     

    Has anyone found a way to work around this?

    Wednesday, March 19, 2008 12:16 PM
  • drop & recreate them?

    it's a shame the schema compare tool doesn't have different types of "different" marking -- since there's 'Different - but if you run a build I won't change anything' and there's 'Different - and I will force this to be changed if you run a build'
    • Marked as answer by StevenPo Tuesday, November 11, 2008 9:36 PM
    Wednesday, March 19, 2008 3:10 PM