(reposted from http://blogs.msdn.com/lbruck/archive/2006/05/25/607029.aspx)
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:
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:
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#.