I’ve had this tendency to deploy CLR Functions manually by providing the dll and scripts to the data management team. This has generally worked out great, and I’ve always thought I’d have a dba available to perform the database duties. Imagine my surprise when I could not contact anyone today. Apparently database administrators take their holidays and vacations seriously (it’s Independence Day weekend for my non-American readers).

Another developer asked me why I couldn’t deploy my clr functions through the IDE. Having never done this, I asked him how. He showed me the deploy button in his project.

I went back to my desk, heartened that I could still get my work done. I opened the Solution, right-clicked the project… but there was no Deploy. Thinking back on it, I know that I tend to make class libraries rather than database projects. I looked at my coworker’s project and sure enough, he was using a database project.

It seemed rather heavy-handed to create a new database project and move files over, so I dug into the csproj file to figure out how to convert it.

There are two key things that need to be done to convert a class library to a database project. SqlServer.targets needs to be imported, and the ProjectTypeGuids property needs to be added.

  <Import Project="$(MSBuildToolsPath)\SqlServer.targets" />
  <PropertyGroup>
    <ProjectTypeGuids>{c252feb5-a946-4202-b1d4-9916a0590387};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>
  </PropertyGroup>

It doesn’t matter where these entries are added, but I place the property in the top property group below the Platform property and paste the import near the CSharp.targets import. The first guid in ProjectTypeGuids specifies that this is a SqlClrProject. The second guid specifies this is a C# project. Visual Basic developers will need to replace the C# guid with F184B08F-C81C-45F6-A57F-5ABD9991F28F.

The deploy button now appears but doesn’t work. When clicked, it fails with no more information than “Deploy failed.” Deducing that it probably needs a database connection, I used MSDN to find out where that configuration is located. This is one place the MSDN documentation is misleading.

Select Deploy from the Build menu. The assembly will then be registered in the SQL Server instance and database specified when the SQL Server project was first created in Visual Studio.

This is true only if one has never changed that configuration for the Database project. Since this was a class library converted to a database project, the server and database wasn’t specified when creating the project. Luckily, this can be configured in the Properties window on the Database tab. Be sure to add the connection to Server Explorer (Ctrl+W, L) first.

At this point, the project has been converted and we’re almost ready to deploy. Before doing so, the assembly must be dropped from the database. Visual Studio is smart enough to drop it, but only if the assembly was deployed from Visual Studio in the first place. After the first deployment, it will work automatically in the future.

While building a new Web Deployment project, I received the error "Unable to build project output group 'Content Files from (Active)." I checked all of my settings and everything seemed to be correct.

I found a support article on Microsoft's Help and Support site describing why this occurs. Apparently, if you have missing files in your project this will happen. In this case, it turns out that a folder of images had been moved in the source tree but not in the project file. This was causing them to all show the the yellow exclamation symbol. Even though the project has "warnings as errors" turned on, the compiler will not complain about missing content files since it does not need to do anything with them.

If you get the error, quickly scan the project you're deploying for any missing files. It will be a file with the Build Action set to Content.