Schema Comparison throwing an error

Question

Hi I have a SSDT project which is targeted for SQL 2005. When I do a Schema Comparison it throws an "Object reference not set to an instance of an object". My question really is why in the Schema Compare Options the heading says "options for SQL Server 2012" - Is this the reason why this error is thrown? If so how do I change the option from SQL Server 2012 to 2005?

---

Sridhar

Answer 1

Hi Sridhar,

Schema Compare shows the same options page regardless of the SQL version of the source and target. For Schema Compare, the SQL 2012 object types and options are a superset of the SQL 2005 object types and options, so you have ability to filter on any SQL 2005 type and enable any option you need. I agree that this heading is misleading, however this is not causing the error you are describing.

Could you provide some more details on the source project and target database as well as the sequence of operations you are performing? I'd like to see if I can reproduce the issue here.

Thanks,

Tony.

Answer 2

Hi Tony

I have created 2 SQL Server 2005 SSDT projects from Visual Studio 2010 (SQL Server Object Explorer > Right Click > Create New Project..).

I have added Project A as a Database reference in Project B.

After this I have introduced additional objects and have made changes to both Projects A& B. The end goal for me is to synchronise the target databases (SQL 2005) with the changes that I have made.

I am able to Schema compare Project A with the target database however when I attempted the same for Project B it failed with "Object reference not set to an instance of an object". A blank screen

Since your last post I went to the Schema Compare options and unchecked all and selected only Table comparison for Project B. This worked fine without any errors.  I then decided to Reset all the options to default and performed the schema compare, this time the schema compare gave Error "Object reference not set to an instance of an object". Not too sure if this means anything?

Regards

Sridhar

---

Sridhar

Answer 3

Hi Sridhar,

Could you summarize what kinds of objects you have in Project B and which objects refer to Project A? It would appear that one specific kind of object is triggering the issue.

Tony.

Answer 4

Hi Sridhar,

At this stage I am unable to reproduce the problem here. I'd be happy to investigate this issue further if you are still experiencing this problem.  Please follow up here, or to me directly (tgedge at microsoft dot com) if you need further assistance. If it is at all possible for you to share the projects and DACPACs of the databases causing the problem with me directly, that would be extremely helpful.

Tony.

Answer 5

Hi Tony,

I have the same problem "Object reference not set to an instance of an object".

I use Visual Studio 2010 Professional Edition SP1 with SQL Server Data Tools (September 2012 update).

I have SQL Server 2008 database (myDatabase2) with database reference to other SQL Server 2008 database (myDatabase1) on the same server.

I created database project for each database with SQL Server 2008 as target platform. For myDatabase2 project I added database reference to myDatabase1 project, and manually replaced all 3-part names like myDatabase1.dbo.myTable to [$(myDatabase1)].dbo.myTable. Project rebuilded successfully.

When I compare myDatabase1 schema between project and database itself, everything looks good. But when I try to compare myDatabase2 schema between project and database (any database, I tried myDatabase1 and myDatabase2), I receive error "Object reference not set to an instance of an object".

When I compare database myDatabase1 with myDatabase2, everything works good, so the problem is not in database, but in project.

When I compare project myDatabase2 with itself, I still get the error.

The only case when error doesn't appear is when I compare project myDatabase2 with project myDatabase1. I believe the error doesn't appear because both myDatabase1 and myDatabase2 were parsed during the comparison, and database reference was parsed correctly.

Hope it can help to fix this error.