Visual Studio 2008 Database Edition schema refactoring causes "Object Reference not set to an instance of an object"

I recently had a problem in a SQL 2008 database project. I am using Visual Studio 2008 Team Suite (which include the database Edition) with Service Pack 1 and the GDR2 database edition update installed.

The problem was that no refactorings would work. By refactorings I am referring to the refactor options available from the Data menu or by right clicking a database object from the Schema View window of Visual Studio 2008. These include options such as Rename and Move schema.

I would try to refactor an object, say to rename it. I would get the first dialog asking what I want to change and allowing me to select extra options such as Preview changes, Generate refactoring log, etc. Upon clicking OK from this dialog I would always get a further error dialog windows stating "Object Reference not set to an instance of an object.". This occurred every time, for any object that I tried to refactor, for any type of refactoring. The error dialog was also of the old style without the nice options to see the full error information or stack trace that is more common in most Visual Studio 2008 error dialogs.

I attached a second instance of Visual Studio to the first instance and tried the refactoring again. The second instance managed to intercept some errors that were being thrown by the first instance and they consisted of multiple errors like the following;

A first chance exception of type 'antlr.NoViableAltForCharException' occurred in Microsoft.Data.Schema.ScriptDom.Sql.dll
A first chance exception of type 'antlr.MismatchedTokenException' occurred in Microsoft.Data.Schema.ScriptDom.Sql.dll
A first chance exception of type 'System.NullReferenceException' occurred in Microsoft.Data.Schema.Sql.dll

I was getting a bit out of my depth here so started a thread on the Microsoft Visual Studio Team System Database Edition forum asking for help.

After some forum discussion and guidance with Joyce Wang of Microsoft and further investigation by me to pinpoint the issue, the problem turned out to be a very obscure problem that they were aware of but does not occur very often.

I had a post deployment SQL script in my project that used a CTE (Common table Expression) to load a Numbers table. Joyce explained that there is a bug in the VS 2008 GDR editions where post deployment scripts with CTE's cause this refactoring error, even though the script is valid and the database project both builds and deploys correctly. Joyce further explained that there is a way to not cause the refactoring error by adding a semi colon after the CTE closing bracket, but of course this then causes the script to no longer work when deployed as it is not the correct SQLsyntax for a CTE based query.

The workaround is to exclude the pre or post deployment script from your database project, action all the refactorings you need to do, then include the script back in the project. Clunky but it gets the job done.

This is apparently fixed in Visual Studio 2010 so I'll have to have a look at that soon.

So, essentially this post is trying to explain that if you get this Object Reference error when refactoring, the most likely culprit is a pre or post deployment script that contains a CTE and the workaround is to exclude the script from the database project while you are doing your refactoring, then include it back in when you are done.