Error: Cannot insert duplicate key row in object 'dbo.Solutions'

A quick post to talk about an error I just found while working with a sandbox solution in SharePoint 2010 (and when I Bing’ed and Googled it I got nothing, literally nothing!). Here is the scenario …

You have a sandbox solution in Visual Studio 2010 and you are quite happily deploying it in to your site. Now lets say you delete that site collection and create a new one with the exact same URL and same name, and then try to redeploy your sandbox solution right from Visual Studio. You will get the following error:

Cannot insert duplicate key row in object 'dbo.Solutions' with unique index 'Solutions_BySiteAndSolutionId'.  The statement has been terminated.

How do I fix this you ask? Simple – in Visual Studio in the solution browser, select the project item for your sandbox solution and look in the properties window. You will see the URL property that tells Visual Studio where to deploy your project. Simply change this to something else and then change it back to its original value. Hit deploy again and all will be well.

Now for the explanation … I don’t really have one. I do know that Visual Studio does some crazy voodoo stuff when it is talking to the locally installed instance of SharePoint sometimes, and it appears that the way it goes about deploying and activating a sandbox solution is no exception to this. The error leads me to believe that something is happening to keep the solution in the database even after the site collection is deleted (if you look through the ULS logs you will see it is a SQLException that is being thrown here), I just can’t think of a good reason for why that would happen though. At least it’s easy to fix though!