SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Add to Technorati Favorites Add to Google
 

Hello SMO (F#) World!

By cmille19 in Chad Miller | 01-13-2010 7:56 PM | Categories: Filed under: , ,
Rating: (not yet rated) |  Discuss | 2,072 Reads | 1273 Reads in Last 30 Days |6 comment(s)
Reading The F# Survival Guide has motivated me to write my version of an F# "Hello World!" utility. What I mean by that is to write something simple that I've written in other programming languages as a learning exercise. In my world of databases I use SMO (pronounced smoh or S-M-O). One of the easiest things I can do is  write some code to script out SQL Servers tables.
 
I'm going to use the F# command-style interactive console, fsi.exe that ships with F#. The only installation needed is F# and SMO version 10 that is included with SQL Server 2008 Management Studio. On my machine using the Oct 2009 CTP version the path to fsi.exe is C:\Program Files\FSharp-1.9.7.8\bin. To run the interactive console open a command windows and navigate to the bin directory and run fsi.exe. Once in the interactive console you can either type or paste the F# code to run. Let's take a look at the code and then I'll provide a short explaination:
 
#I @"C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\";;
#r "Microsoft.SqlServer.Smo.dll";;
#r "Microsoft.SqlServer.ConnectionInfo.dll";;
open Microsoft.SqlServer.Management.Smo
open Microsoft.SqlServer.Management.Common
let svr = Server(@"Z002\SQL2K8")
let db = svr.Databases.["pubs"]
for t in db.Tables do
    for s in t.Script() do
    printfn "%s" s;;
 

Notes

  • The first three lines are not comments, they are used to resovle the assembly path and reference the SMO assemblies. These lines are specific to the interactive console if you're using Visual Studio you would add references as you would normally.
  • F# is case sensitive
  • Whitespace is important
  • You use a dot before brackets to access an element, which is different than other languages
  • Double semi-colons terminate a command in the interactive console
  • The @ sign is used for verbatim strings (here-strings) -- used to escape special characters.
  • The above example isn't very F#-like which favors functions and recursion over imperative looping, but this just a simple example
  • Although it may not look like it, F# is strongly typed. It uses type inference to determine type. You can explicitly type items

EDIT Jan 24, 2010: Tony Davis blogged about this post in his article Life at the F# end providing a revised solution that is more F#-like as follows:

db.Tables
   |> Seq.cast
   |> Seq.collect (fun (t:Table) -> t.Script() |> Seq.cast)
   |> Seq.iter (fun s -> printfn "%s" s);;

You'll need to read the article for an explanation of the F# code. Tony also suggests F# as a common scripting language for both developers and administrators. My thought on the subject is that Powershell is the common scripting language for administrators, but perhaps F# may have a niche use case for administrators needing better scale--I would love to see more practical examples of F# administration scripts. Be sure to read the comments section in which I respond with my reasons for exploring F# out of a need to achieve some concurrency missing  from Powershell. Oh, and I also appologize for making someones' teeth itch with my use of imperative looping in F# Open-mouthed

Comments
 

Tim Mitchell said:

Thanks for sharing - I don't think I've ever even seen an example of F# before.

January 14, 2010 1:51 PM
 

cmille19 said:

There's something strangely satisfying about doing a google search for F# SMO and seeing this little blog post hit #1.The free e-book "The F# Survival Guide" is a quick and easy read if you're interested in getting started with F#.

January 14, 2010 5:58 PM
 

Tony Davis said:

In a fascinating interview in this issue of Simple-Talk, Don Syme discusses his work on the F# programming...

January 21, 2010 7:50 AM
 

Tony Davis said:

Hi Chad,

I really enjoyed your piece, and I hope my editorial didn't come across as in any way dismissive of your solution. It works fine; I was just intrigued to find out what a more "F#-like" solution would look like and to see how much truth there was in the idea that it was an approach "more intuitive to SQL developers". I think the jury is still out!

Anyway, thank you for inspiring me to take a closer look at F#!

Cheers,

Tony.

January 28, 2010 6:33 AM
 

cmille19 said:

Tony,

As you folks say, "No Worries." I didn't take offensive to your post in the least in fact I was pleasantly surprised to see F# code closer to what I wanted to do.

I'm not sure about the intuitive part either.

Thanks for sharing

January 28, 2010 1:23 PM
 

F# February 2010 CTP « Sql From Hell.com said:

Pingback from  F# February 2010 CTP « Sql From Hell.com

February 20, 2010 2:55 AM
Leave a Comment
Only members of SQLServerCentral may leave comments. Register now for your free account or Sign-In if you are already a member.