Installing Kiln For Your Server’s SQL Extensions Manually



When installing Kiln 2.9 or later, you may have received a message asking you to install custom CLR (Common Language Runtime) functions. This article explains how this can be resolved by installing the SQL extensions manually.


Kiln versions 2.9 and above.


For example, Machine M hosts the Kiln website and it talks to SQL Server S, which hosts the Kiln database.

  1. Go to \Program Files\Kiln\website\SqlExtensions on the machine M and copy SqlExtensions.dll to a location on server S’ filesystem.
  2. Remember the path to the DLL file on server S.
  3. Ensure that if your Kiln database is not called “kiln”, update the line use kiln; to point to the right one.
  4. Once on that server, run the following SQL script:
EXEC sp_configure 'clr enabled', 1; RECONFIGURE

use kiln;

CREATE ASSEMBLY KilnSqlExtensions FROM N'\path\to\SqlExtensions.dll'
WITH permission_set = safe
CREATE FUNCTION KilnSplitBigints(@list nvarchar(MAX)) RETURNS table(ix bigint)
AS EXTERNAL NAME KilnSqlExtensions.SqlExtensions.KilnSplitBigints
CREATE FUNCTION KilnSplitBinaries(@list nvarchar(MAX)) RETURNS table(ix binary(20))
AS EXTERNAL NAME KilnSqlExtensions.SqlExtensions.KilnSplitBinaries

Common Questions

Why does Kiln need to enable CLR and create these two functions?

  • As a part of ongoing performance work in Kiln, we consolidated many “n + 1” SQL queries into a single query.
  • Unfortunately, these consolidated queries often contain large lists of integers and binary data that interfere with the database query planner’s ability to cache query plans.
  • Query plan caching is a big win and notably increases the performance of frequently issued queries; losing it was a huge blow for us.
  • As a result, these two functions are used in new queries so that they remain cache-friendly.

Does Kiln require CLR to be enabled?

  • Yes! At this time Kiln will not work without enabling CLR.
  • If you are unable to enable CLR, please contact our friendly support staff to find the best resolution to your problem.