"Object reference not set to an instance of an object" error

sethpetryjohnson

When I compare a live database to a scripts folder I get an error at the end of the comparison. The error is "Object reference not set to an instance of an object" and it occurs during the "Registering Databases" stage.

The program indicates that it was processing a specific function's .sql file, but I don't think that function is the problem. It's identical in both places and has compared fine numerous times in the past.

This particular comparison project has been working fine, the only recent changes are (1) modifications to a number of DB objects, and (2) I enabled source control integration prior to the compare.

I'm using SQL Compare 6.2.0.271, Professional Edition. The live database is SQL Server 2005, the scripts folder contains .sql files for every object in the database.

Any suggestions on how to troubleshoot this?

Thanks,
Seth Petry-Johnson
IRTH Solutions, Inc.

sethpetryjohnson

My scripts folder is organized into Function, View, Table, etc. folders. We added an additional folder called "Migration Scripts" to store custom data migration scripts that pertain to a specific changeset. (The entire folder structure is version controlled)

It seems pretty obvious to me now, but storing these scripts in the same directory structure as our actual DDL files is a bad idea. SQL Compare examines the entire directory structure looking for script files defining objects to compare and apparently gets tripped up by the migration scripts.

The solution: either store migration scripts up one level in the tree, so the don't exist in SQL Compare's target directory, or just wrap the migration scripts in comment tags.

David Atkinson

Yes, you're right. SQL Compare 6 Pro doesn't care where the script files are and will try to parse all .sql files. This ultimately allows maximum flexibility for you, as you can create any folder structure you like, and you can equally split up your objects to any level of granularity as long as you place them in .sql files.

As you said, putting such files in a folder at the same level as the top scripts folder is the solution. In future a possibility may be to consider an option forcing SQL Compare to ignore certain folders. We should also improve the error messages to be more helpful to help you to pinpoint the cause of your issue.

Thanks a lot for your feedback.

David Atkinson
Red Gate Software