Skip to content
Tags

, ,

F# on SqlClr

2011-08-03

I thought I’d start this blog off with a post on how I got my F# code to run in SqlClr. Why would you want to do this? If you’re dealing with huge amounts of data and need to process that data in ways that are not suited to Sql, it might help performance-wise if your code can be ‘closer’ to the data. Do read more about it: Introduction to SQL Server CLR Integration

I was starting this new project that requires lots of data analysis and I really wanted to use F# instead of C# because of the many benefits it brings.

Let’s begin. Create a database to house your stored procedure. In order to run Clr code on it, run the following script against the database. We will need to deploy FSharp.Core which is not marked as trustworthy by Sql Server. That is why we run the ‘set trustworthy on’ bits.


-- Enable Sql Clr
sp_configure 'clr enabled', 1
reconfigure
go

-- Avoid having to sign assemblies and worry about other security issues
-- Dont forget to change 'DatabaseName' below.
alter database DatabaseName set trustworthy on
go

Create a new F# Library. Make sure you target .Net 2.0 as this is what current versions of Sql Server run. The upcoming Sql Server ‘Denali’ will run .Net 4.0.

Now add the code for a simple stored procedure:

namespace SqlClr
open System
open System.Data
open System.Data.Sql
open System.Data.SqlTypes
open System.Data.SqlClient
open Microsoft.SqlServer.Server

type SqlClrSample =
   [<SqlProcedure()>]
   static member Hello (x:string) =
      SqlContext.Pipe.Send("Hello " + x)

This code doesn’t require much explanation. We create a static method that has an SqlProcedureAttribute applied to it. SqlContext.Pipe.Send sends a message directly to the client. There are other interesting methods in SqlContext.Pipe, but I’ll leave that for later.

Build the assembly. Now we must register the assembly in Sql Server in order to run this code. We also need to deploy any referenced assemblies. In our case, there is one, FSharp.Core.
When you do SqlClr projects in C#, Visual Studio will deploy the assembly for you. In F#, we will need to do this ourselves. Make a Deploy.sql file in your project. I like to keep this file in the project even though it doesn’t get built, so that it is versioned along with the rest of the project. Sql script for registering follows.

-- First, drop everything in backwards order
IF  EXISTS (SELECT * FROM sys.procedures procs WHERE procs.name = N'Hello')
    DROP PROCEDURE Hello
GO

IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlClr')
    DROP ASSEMBLY [SqlClr]
GO
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'FSCore')
    DROP ASSEMBLY [FSCore]
GO

-- Now, create everything in forwards order
CREATE ASSEMBLY FSCore FROM 'C:\Program Files (x86)\Reference Assemblies\Microsoft\FSharp\2.0\Runtime\v2.0\FSharp.Core.dll' WITH PERMISSION_SET = UNSAFE
GO
CREATE ASSEMBLY SqlClr FROM 'D:\robert\dokument\visual studio 2010\Projects\SqlClr\SqlClr\bin\Debug\SqlClr.dll'
GO

-- External name is [SqlAssemblyName].[Full typename].[Method name]
CREATE PROCEDURE [dbo].[Hello]
   @name [nvarchar](64)
AS
EXTERNAL NAME [SqlClr].[SqlClr.SqlClrSample].[Hello]
GO

Some things of note:

  • We need to manage dependencies ourselves. This means that in order to be able to repeat this when we make changes, we need to drop everything we created the last time in backwards order. Procedure ‘Hello’ can not exist without assembly ‘SqlClr’, which in turn can not exist without assembly ‘FSCore’. If this is done in the wrong order the script will simply fail. Once everything is dropped, we create everything in forwards order.
  • There are hard-coded paths in this script. I haven’t really found a good workaround for this yet. Suggestions are welcome!

Run this script against your database. Now you should be able to run your procedure:

Now, there are more useful forms of Sql Clr code and I’ll hopefully have more to say on the subject at a later date. Sample project is here.

Update: Niels Berglund informs me that we don’t have to use SqlProcedureAttribute from F# as it is only used for deployment from VS. I’ll still keep them in my project for clarity.

Advertisements

From → Code

2 Comments
  1. 7sharp9 permalink

    Nice first post!

    How about a real world example of where this would be really useful?

    I have always fancied doing code inside sql server but never found a compelling reason…

  2. Thanks!
    I’ll hopefully get to that in coming post(s).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s