Tag Archives: SQL Server

Enabling Danish for SQL Server FullText

SQL Server FullText enables you to search large amount of strings fast, and it is easy to use. It hasn’t changed much since SQL Server 2000.
A simple getting started tutorial can be found on Code Project.

SQL Server FullText is easy to use in applications requiring string searching.

The Danish, Polish and Turkish wordbreaker and stemmer implementations for SQL Server FullText is not developed by Microsoft and therefore not enabled by default. The libraries are however part of the installation process and are therefore present on disk.

To make use of the Danish language capabilities in SQL Server 2008, register the libraries in registry and reload the FullText languages:

  1. Download & run the DanishFulltext.reg file on the server. It will register wordbreaker, stemmer and default location of the thesaurus xml file.
  2. Run the exec sp_fulltext_service ‘update_languages’ in a Management Studio.

Now verify that Danish is enabled with this query: SELECT name FROM sys.fulltext_languages

Note: The DanishFullText.reg assumes that SQL Server is a default instance (not a named instance). If not, modify the file by changing the MSSQL10.MSSQLSERVER to the instance name.

It is the same case with Polish and Turkish – they are not registered by default. See more in the MSDN article How to: Load Licensed Third-Party Word Breakers.

List of out of the box SQL Server 2008 FullText supported languages: Arabic, Bengali (India), Brazilian, British English, Bulgarian, Catalan, Chinese (Hong Kong SAR, PRC), Chinese (Macau SAR), Chinese (Singapore), Croatian, Danish, Dutch, English, French, German, Gujarati, Hebrew, Hindi, Icelandic, Indonesian ,Italian, Japanese, Kannada, Korean, Latvian, Lithuanian, Malay – Malaysia, Malayalam, Marathi, Neutral, Norwegian (Bokmål), Polish, Portuguese, Punjabi, Romanian, Russian, Serbian (Cyrillic), Serbian (Latin), Simplified Chinese, Slovak, Slovenian, Spanish, Swedish, Tamil, Telugu, Thai, Traditional Chinese, Turkish, Ukrainian, Urdu, Vietnamese.

Miracle Open World 2010 Lucene Presentation

The conference is over and it was a great success. I meet a lot of new people and had lots of technical discussions about .Net, graph databases, freetext search, SQL Server, Oracle Service Bus, debugging with WinDbg and extensions.

The slides and demo code for my Lucene session is available here:

My session “Making freetext search with Lucene.Net work for you” 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.

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:

SELECT @@VERSION

SELECT SERVERPROPERTY('ProductVersion'),
       SERVERPROPERTY('ProductLevel'),
       SERVERPROPERTY('Edition')

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:

RTM SP1 SP2 SP3 SP4
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 SqlServerCentral.com:

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 🙁