Migrating SQL CLR project from VS2008 to VS2010 - deploy fails

Question

I have a solution with a SQL CLR project in VS2008. The solution / project was converted to VS2010 and now the deploy no longer works. I get the following type of erros:

Beginning deployment of assembly MyProjectDatabaseCode.dll to server DEVPN-004 : MyProject The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version. C:\inetpub\wwwroot\VS2010_MyProjectSolution\DatabaseCode\bin\Release\MyProjectDatabaseCode.dll : Deploy error : Plan verification encountered errors; deployment cannot continue. C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.SqlClr.targets(96,5): Deploy error TSD00561: This deployment may encounter errors during execution because changes to [dbo].[Column1] are blocked by [dbo].[Table1].[Column1]'s dependency in the target database.

I verified and the project is targeted against .NET 3.5. We have predeployscript.sql and postdeployscript.sql files that are supposed to be run. It appears like they are not being run by VS2010. What could be causing the issue?

Answer 1

I'd recommend using Reflector to take a look at your assembly in the build output directory. If you see 2 references to mscorlib, then what has happened is that a phantom reference to the 4.0 framework has been added to the project. If you see this problem, you may be able to get rid of the 4.0 reference by hand editing your project file. Open the project in an XML editor, and look for the following line:

<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="2.0">

Change ToolsVersion="2.0" to ToolsVersion="4.0"

This helped me with a similar upgrade problem, but your mileage may vary. If you don't see the phantom mscorlib reference, you probably have another issue.

Answer 2

We ended up contacting Microsoft and they reproduced our issue as a known bug. They offered a workaround by retargeting the assembly to .NET 2.0, but that didn't work in our scenario either. It seems that the only way forward until the fix is to create a brand new SQL CLR project in 2010 and add all the files to it by hand.