SQL Server Deployment – Reference Assembly in sqlproj Not Deployed

database-projectssql serversql-clrvisual-studio-2013

I have a Visual Studio 2013 Database Project which takes a modified version of fastJSON as a reference. I selected Generate DDL in the reference properties as shown in this screenshot:

fastJSON property panel

And in the .sqlproj msbuild file:

<Reference Include="fastJSON">
  <HintPath>..\..\fastjson\output\net40\fastJSON.dll</HintPath>
  <GenerateSqlClrDdl>True</GenerateSqlClrDdl>
</Reference>

However bin/debug/Project.sql does not contain a line for CREATE ASSEMBLY fastJSON . . .. Adding the assembly manually works and my project will then deploy and run. What do I do to get Visual Studio to deploy my assembly?

Best Answer

I played around a bit to figure out what these settings do... when I commented, I only mentioned what our settings are without understanding them; I'm not the project lead for our migration to Database Projects, so I wasn't familiar with the minutiae of this stuff.

The Model Aware property needs to be set to True.

This property is poorly documented, but from my understanding, enabling it will expose the objects within the assembly to the database model so they can be used in the SQLCLR wrappers (i.e., CREATE ... EXTERNAL ...) and the references can be resolved. If the project doesn't include these objects in the model, there's no way they'll be scripted in the output. Also, if there's a reference to an object in the assembly and this property is set to False, the project will fail to build.

The Generate Sql Script property I mentioned is in a similar state of documentation. This controls whether the publicly visible members in the assembly are automatically scripted into the output. We have it disabled to retain the flexibility of renaming the SQLCLR wrappers and selectively including only the objects we need. Which way you set this is up to your discretion.

Hopefully that helps the public documentation of these properties!

Related Question