Posts Tagged ‘SQL Server’

« Older Entries

Enabling Danish for SQL Server FullText

Monday, August 9th, 2010

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.

Tags: , , , , ,
Posted in Full Text Search, MS FullText, SQL Server | No Comments »

Miracle Open World 2010 Lucene Presentation

Tuesday, April 20th, 2010

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.

Tags: , , , , ,
Posted in .Net, Announcement, Full Text Search, Lucene, SQL Server | 2 Comments »

SQL Server build version

Wednesday, March 17th, 2010


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
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

Tags:
Posted in SQL Server, Useful tools | No Comments »

Transferring SQL Server logins

Monday, March 1st, 2010

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 :-)

Tags: , ,
Posted in SQL Server, Security | No Comments »

Reuse in SQL Server 2008 Integration Services

Thursday, October 22nd, 2009

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 :-(

Tags: ,
Posted in SQL Server | No Comments »

Change Data Capture (CDC) in SQL Server 2008

Tuesday, August 11th, 2009

I where planning to write a blog post about Change Data Capture (CDC) in SQL Server 2008, but then I stumbled upon Pinal Dave’s article Introduction to Change Data Capture (CDC) in SQL Server 2008.

It is a thorough overview of Change Data Capture (CDC).

MSDN reference for Change Data Capture (CDC).

Tags: ,
Posted in SQL Server | No Comments »

Loooong running sql statement

Monday, April 6th, 2009

Sql Server Profiler renegate statement

I was using SQL Server Profiler on a SQL Server 2005 Enterprise Edition looking for performance culprits, when I stumbled upon this very long running process.

I think this must be a world record. :-)

This statement has only used 16 milliseconds of CPU but it has been running for more than 500.000 years!

Tags:
Posted in Code fun, SQL Server | No Comments »

Scary SQL Server 2005 & 2008 bug

Saturday, March 21st, 2009

Watch out when using @@IDENTITY and SCOPE_IDENTITY() in your applications. Below is a quote from a Microsoft SQL Server engineer.

… whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistently and can’t be relied upon.

You can find more about the bug and workarounds at the SQL Server bug  report.

UPDATE 28MAR2009: Pinal Dave has a more detailed blog post about the bug with workaround.

Tags:
Posted in SQL Server | No Comments »

How to check for and enable read committed snapshot isolation

Wednesday, March 18th, 2009

Run the below query to see which databases that uses read committed snapshot isolation:

SELECT name, is_read_committed_snapshot_on FROM sys.databases

To enable it on a specific database run the below query:

ALTER DATABASE [<databasename>]
SET READ_COMMITTED_SNAPSHOT ON

As a developer I am in love with read committed snapshot isolation feature for SQL Server 2005+. Transactions can read and write simultaneously the same piece of data – no read/write contention due to row versioning.

Normal conflicting scenarios:

Simultaneously write and read of the same data.

  • Read committed isolation:  The reader will wait for the writer to release the exclusive lock.
  • Read committed snapshot isolation:  The reader will not block, but receive the latest committed data (latest row version).

Simultaneously writes of the same data.

  • Read committed isolation: The second writer will wait until the first transaction completes because of an exclusive lock.
  • Read committed snapshot isolation: The first transaction committing will “win” and if the second tries to commit, it will abort and error will be raised.

In effect this is optimistic concurrency control without coding anything. Read more about read committed snapshot isolation and isolation levels in the Isolation Levels in SQL Server 2005 article.

Tags: ,
Posted in SQL Server | 1 Comment »

SQL Server 2008 System Views poster

Sunday, February 1st, 2009

Microsoft has released a poster for download, where you can see all the system views for SQL Server 2008 and the relationships between them.

Microsoft also released the system views poster for SQL Server 2005 a couple of years ago.

You need a huge printer/plotter to print these…

Tags:
Posted in SQL Server, Useful tools | No Comments »

« Older Entries