Month: October 2010

Take the formulas 1 + 2 x 3 and (1 + 2) x 3; two completely different answers. In the case of Visual Studio Schema Compare, what I would assume the order of operations to be for updating a database would be to first update tables, then updates views (which could be dependent on the tables), and finally update stored procedures (which could be dependent on either the tables or the views). It seems that Schema Compare attempts to do stored procedures first, and when it comes across a reference to a database object that does not yet exist, it dies out with the following error message.

In this example, we have two tables which have been created and the corresponding Create and Update stored procedures which interact with the new tables. This is a pretty basic example of typical database schema updates and one I would think Schema Compare could figure out.

The current workaround I have found for this is to:

1. Mark all Stored Procedures as Skip All.
2. Run Write Updates.
3. Refresh the compare.
4. Mark all stored procedures as Create/Update All.
5. Run Write Updates.

A few extra steps, but it seems to work every time. Hopefully this is something that Microsoft will correct or provide some information as to why the order of operations proceeds with stored procedures first.

I’ve been using Visual Studio 2010 Schema Compare for deploying database changes from one environment to another (development to staging, staging to production), but I have ran into an annoying bug that I would love some enlightenment on.

I am trying to compare a database on our staging SQL server to our production server and after selecting the two databases and clicking “Compare”, Visual Studio immediately crashes.

Problem
signature:

Problem Event Name: CLR20r3

Problem Signature 01: devenv.exe

Problem Signature 02: 10.0.30319.1

Problem Signature 03: 4ba1fab3

Problem Signature 04: Microsoft.Data.Schema.Sql

Problem Signature 05: 10.0.0.0

Problem Signature 06: 4ba1d812

Problem Signature 07: 2f6c

Problem Signature 08: 24

Problem Signature 09: System.NullReferenceException

OS Version: 6.1.7600.2.0.0.256.48

Locale ID: 1033

 

Additional
information about the problem:

LCID: 1033

 

Read
our privacy statement online:

http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

 

If the
online privacy statement is not available, please read our privacy statement
offline:

C:\Windows\system32\en-US\erofflps.txt

What’s odd about this particular problem, is that other databases on the staging server can be compared to their production counterparts without problem. Additionally, this particular database was able to be backed up and restored to our development environment and a schema compare could be ran between development and production.

If anyone has any tips on this, please leave a comment explaining. If you’re having this issue, please note that as a workaround, it seems to be working to backup and restore the database. I also plan on posting this to Microsoft as well and I will update the post if I find anything.