Overview

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.

Environment

Kiln versions 2.9 and above.

Resolution

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
GO

use kiln;

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

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.