Category Archives: SQL Server

Speaking about Lucene at Miracle Open World 2010

The conference Miracle Open World 2010 is soon upon us at Legoland (April 14.-16.) 🙂

There will be four tracks this year: Oracle track, SQL Server track, .Net track and a workshop track.

The conference is legendary because time spend at the conference is divided between 80% technical stuff and 80% social networking. No kidding’ socializing is a big part of this conference with gala-dinner and the not-to-miss beach party at Lalandia Aquadome (including drinks).

This year I only have one session where I’ll be presenting Lucene.Net.

Session abstract:

Lucene is an open source full-featured text search engine library, making searching in large amounts of text lightning fast. Lucene are in use by many large sites like Wikipedia, LinkedIn, MySpace etc.

It is easy to get started with Lucene, but there are many pitfalls… In this session you will learn about the do’s and don’t’s for indexing and searching, tools, scaling, new features in version 2.9 and some of the more advanced features.

This presentation will use the Microsoft .Net implementation of Lucene named Lucene.Net, but the content of this presentation applies for ported versions of Lucene.

At the time of writing, 207 participants have registered for the conference. You can still register – it’s not too late.

See more at the Miracle Open World 2010 site.

SQL Server build version

Working with SQL Server it is often important to know which edition, version and service pack applied to the instance.

This information easily retrieve with either of these two system functions ServerProperty or @@Version:



Both of the returns roughly the same information, but I tend to use the @@Version function as it easier to remember and type.

With the ServerProperty function additional information can be retrieved like MachineName, InstanceName or BuildClrVersion. See more about the ServerProperty function on MSDN.

From the build number alone it is possible to figure out which version of the SQL Server and Service Packs applied via the below table:

SQL Server 2008 R2 10.50.1600.1
SQL Server 2008 10.00.1600.22 10.00.2531 10.00.4000
SQL Server 2005 9.00.1399.06 9.00.2047 9.00.3042 9.00.4035
SQL Server 2000 8.00.194 8.00.384 8.00.532 8.00.760 8.00.2039

Credit for the above table is due to this site.

Update April 30th 2010: Added SQL Server 2008 R2 RTM build number

Update October 4th 2010: Added SQL Server 2008 SP2 build number

Transferring SQL Server logins

I had to migrate a SQL Server 2008 instance on to new hardware.

I choose to detach the databases and attach the databases on the new SQL Server 2008 instance on the new hardware. This was easy, but the origin SQL Server instance used mixed SQL Server and Windows Authentication Mode. This also meant that I had to migrate SQL Server logins, as the logins where a mix of SQL Server logins and Active Directory domain accounts/groups.

Both the SQL Server logins and domain accounts/groups has an unique SID (Security Identifier), which ties the logins in the SQL Server with the users in the database.

While the domain accounts/groups resides in the Active Directory domain controllers, the SQL Server logins only resides in the SQL Server. This means, moving domain accounts/groups is easy, by just creating the same users in the new SQL Server instance, but SQL Server logins is not. I could either choose to:

  • Recreate the SQL Server logins with new users in each of the databases, as new SQL Server logins will get new SIDs and therefore not be tied to the old database users. This also requires that you know all the passwords – alternative reconfigure all the client applications using SQL Server logins 🙁
  • Transfer the SQL Server logins with SID and password 🙂

How to transfer SQL Server logins? See the KB article: How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008. Easy 🙂

Reuse in SQL Server 2008 Integration Services

Or lack of 🙁

My current project requires SSIS (SQL Server 2008 Integration Services) packages for ETL processing.

SSIS seems very capable, but lacks fundamental things that a developer like me takes for granted. I did not expect SSIS to have the ability of inheritance as it isn’t object-oriented, but I did expect functions or methods like a procedural language or set-based languages like T-SQL. Sadly the answer is no.

You can make script tasks or script component with custom T-SQL or .Net code, but logic in expressions you have to duplicate.

I goggled reuse and SSIS and found this statement in an article about reuse in SSIS at

Let’s not forget, copy&paste is the first level of code reuse

In essence it’s true, but I would hope the entire Information Technology industry has move way beyond this point years ago.

This post at the Microsoft SQL Server forum confirms this horrific truth about lack of reuse in SSIS 🙁