(reposted from [link:blogs.msdn.com])

Please read my first post to understand the goals and security implications of the samples I am presenting.

I will assume that you are in the sysadmin role on your SQL2005 server, you are using a program that allows you to execute TSQL commands (like Management Studio), and that you are running it on the same machine as your F# installation. Change any paths to be appropriate to your machine.

First of all, you need to turn on CLR support in SQL2005:

1
2
3
4

sp_configure 'clr enabled', 1
go
reconfigure
go

Next, you need to create a database and allow the execution of "unsafe" assemblies in it. There is a more sophisticated (and complicated) method involving signing the unsafe assemblies with a key and then granting the appropriate permissions to that key, but for testing purposes I will go the simple route:

create database fsharp
go
alter database fsharp set trustworthy on
go
use fsharp
go

Now you need to load the two utility libraries used by most F# programs into the database:

create assembly fslib from 'c:\fsharp\fsharp-1.1.11.7\bin\fslib.dll' with permission_set = unsafe
go
create assembly mllib from 'c:\fsharp\fsharp-1.1.11.7\bin\mllib.dll' with permission_set = unsafe
go

Now you are ready to write your first F# stored procedure. Create a file called sqlclr.fs (the name is important) with following code:

1
2
3
4
5
6
7
8
9

open System
open System.Data
open System.Data.Sql
open System.Data.SqlTypes
open Microsoft.SqlServer.Server

[<SqlProcedure>]
let printToday() =
   SqlContext.Pipe.Send(DateTime.Now.ToString())

Compile this into a DLL. To load and test out the procedure, issue the following commands:

create assembly sqlclr from 'c:\fsharp\sqlclr\sqlclr.dll' with permission_set = unsafe
go
create procedure PrintToday external name sqlclr.sqlclr.PrintToday
go
exec PrintToday
go

I won't go into all of the details of SQL CLR integration at this point, I want to point out some important facts. CLR stored procedures have to be static functions; this works well with normal F# functions since they are defined this way. By default, they get created in a class with the same name as the source file; the name of that class must be used as the second part of the external name of the stored procedure.

This has been a lot of setup work for very little immediate reward, but it lays the foundation for what I'll present in future posts. Next, I will demonstrate less trivial (and more useful) user-defined functions in F#.