Anders Lybeckers Weblog!

Enabling Danish for SQL Server FullText

2010-08-09T18:16:30Z

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:

Download & run the DanishFulltext.reg file on the server. It will register wordbreaker, stemmer and default location of the thesaurus xml file.
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.

Java 4-ever

2010-07-18T06:23:25Z

I find this video hilarious…

You should use the best tools at hand to solve the problem. That said; choosing between Java or .Net doesn’t really matter in most cases. There are however some areas where Java is a better choice and vice versa.

I can’t wait to see it in the cinema

PS. I do develop with Java even though I do not blog much about it.

Update: YouTube removed the video due to copyright claims. You can still see it JavaZone.

No related posts.

Meeting the SQL Azure Development Team

2010-06-30T14:28:05Z

Last week I was at Microsoft HQ in Redmond, WA, USA. I was invited by the SQL Azure Development Team to look at some of the new unreleased features and comment on features in their roadmap.

Unfortunately most of the content was confidential, meaning that I was under NDA, so I may not disclose any details. Sorry :-/

During the week with the SQL Azure Development Team I was fortunate to be engaged in technical detailed discussion about some of the upcoming feature releases – mainly discussing the SQL Server features not currently available in SQL Azure. It was interesting and enlightening at the same time to discuss their technical challenges and why they have build SQL Azure the way they have.

All in all, my conclusion after this event is that Microsoft takes SQL Azure seriously and it will become a major player in the RDBMS world. It will not just be a SQL Server in the cloud, but a separate product with different market segments and different features. I am looking forward to a bright future with SQL Azure

Check for breaking changes in APIs

2010-08-09T18:11:24Z

Have you ever had the need to compare interfaces of two versions of the same framework?

If you have, then ApiChange is a tool for you. It’s open source, powerful and easy to use

I gave it a spin comparing current trunk version 2.9.2 of Lucene.Net with the latest official release version 2.4.0.

I downloaded ApiChange and ran the following command in a command prompt:

ApiChange.exe -Diff -old C:\Lucene.Net_2_4_0\Lucene.Net.dll -new C:\trunk\Lucene.Net.dll

The output lists all the differences, but here is a summary:

23 public types where removed
96 public types where added
158 public types where changed

Cool little tool with other features such as:

Diff public types for breaking changes.
Who uses a method?
Who uses a type?
Who uses implements an interface?
Who references me?
What format has the binary (32/64, Managed C++, Pure IL, Unmanaged)?
Search for all event subscribers and unsubscribers.

It’s based on Mono Cecil – a free IL parser, and not reflection as I initial thought. Go check it out…

Levels of reuse in Software Development

2010-06-01T06:52:33Z

One of the promises of object-orientation is reuse. Developing new software systems is expensive, and maintaining them is even more expensive. Reuse is therefore sensible in both business and technology perspectives.

With assistance of Erich Gamma, I have identified four levels of reuse.

First level of reuse: Copy/paste

Duplicating code or functionality makes it easy to reuse it. It’s a real timesaver at first, but keeping all the duplicates up-to-date and maintaining them is horrifying task. Not to mention the problems when forgetting to update one or more duplicates…

“Copy and paste programming is a pejorative term to describe highly repetitive computer programming code apparently produced by copy and paste operations. It is frequently symptomatic of a lack of programming competence, or an insufficiently expressive development environment, as subroutines or libraries would normally be used instead. In certain contexts it has legitimate value, if used with care.” Wikipedia

Second level of reuse: Class libraries

Reuse at class level or a set of classes in a software library is common and also fairly easy with object-oriented languages.

“Libraries contain code and data that provide services to independent programs. This allows the sharing and changing of code and data in a modular fashion. Some executables are both standalone programs and libraries, but most libraries are not executables …” Wikipedia

Third level of reuse: Design Patterns

Patterns allow you to reuse design ideas and concepts independent of concrete code.

“In software engineering, a design pattern is a general reusable solution to a commonly occurring problem in software design. A design pattern is not a finished design that can be transformed directly into code. It is a description or template for how to solve a problem that can be used in many different situations. Object-oriented design patterns typically show relationships and interactions between classes or objects, without specifying the final application classes or objects that are involved.” Wikipedia

Fourth level of reuse: Frameworks

An object-oriented abstract design to solve a specific problem – often very specialized, like Unit Testing frameworks and Object-Relational Mapping frameworks, but can be large, complex or domain specific.

“A software framework … is an abstraction in which common code providing generic functionality can be selectively overridden or specialized by user code providing specific functionality. Frameworks are a special case of software libraries in that they are reusable abstractions of code wrapped in a well-defined API, yet they contain some key distinguishing features that separate them from normal libraries.” Wikipedia

It’s all about being pragmatic – not all software will reach fourth level of reuse and will be structured as frameworks – frankly it shouldn’t. That said; copy/past style development is unquestionably a wrong path.

What level is your company at?

Reuse in SQL Server 2008 Integration Services

Ageing pictogram

2010-08-09T18:12:11Z

I’m in Prague, Czech for the Apache Lucene EuroCon 2010; wandered around, where I saw this drawing on a house wall.

I find it hilarious – especially the natural shadow over the coffins. It’s just by pure coincidence that I was there, at the time of day where the doorway cast its shadow over the coffins

Check for breaking changes in APIs

Finding Missing Indexes with SQL Server DMVs

2010-05-11T14:01:49Z

Finding Missing Indexes with DMVsSome time ago I wrote written about easy index wins for SQL Server 2005.

SQL server maintains statistics about indexes you should consider creating. This time I’ll show you a DMV (Dynamic Management View) that lists index candidates. This method works for SQL Server 2005 SP2 and later versions.

The query is based on three DMVs and returns index candidates where the calculated improvement is more than 10%:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure_pct,
  QUOTENAME(db_name(mid.database_id)) AS [database],
  QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id)) AS [schema],
  QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id)) AS [table],
  'CREATE INDEX [missing_index_' + CONVERT(varchar(64), NEWID()) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns, '')
  + CASE
      WHEN mid.equality_columns IS NOT NULL
	    AND mid.inequality_columns IS NOT NULL THEN ','
      ELSE ''
    END
  + ISNULL(mid.inequality_columns, '')
  + ')'
  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
	  AS create_index_statement,
  migs.*,
  mid.database_id,
  mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
  INNER JOIN sys.dm_db_missing_index_group_stats migs
	ON migs.group_handle = mig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details mid
	ON mig.index_handle = mid.index_handle
WHERE
	migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
		(migs.user_seeks + migs.user_scans) > 10
ORDER BY
	migs.avg_total_user_cost * migs.avg_user_impact *
		(migs.user_seeks + migs.user_scans) DESC

It is important to note, that these are index candidates are only candidates and the improvements are based on estimates. The estimated improvement does not take extra disk space requirements and the maintenance of the indexes during updates, inserts and deletes. Furthermore it does not make recommendation about usage of clustered or non-clustered indexes.

This blog post is inspired by Bart Duncan’s Are you using SQL’s missing index DMVs?

Visual Studio 2010 keyboard shortcuts

2010-05-02T07:39:41Z

I am fond of keyboard shortcuts and wish I was a keyboard-shortcut-ninja and didn’t have to rely on the mouse all the time.

Using keyboard shortcuts boosts productivity and ergonomically a better choice, as the risk of getting a tennis elbow/mouse elbow diminish.

Source of keyboard shortcuts for Visual Studio 2010:

Removing SVN folders with PowerShell

2010-04-24T14:42:57Z

I need to remove.svn folders from an existing Visual Studio Solution a customer email me, so I could commit it to another SVN repository.

If I had access to the original SVN repository, I could have used the export function, as it does not include the .svn folders – but no, it should not be that easy.

What the heck, I have been putting it off way too long to start working with PowerShell. It should be a familiar environment as it is object-oriented with a C# like syntax with full access to the .Net Framework Base Class Libraries (BCL).

Here it goes – my first PowerShell script…

function RemoveSvnFolders([string]$path)
{
    Write-Host "Removing .svn folders in path $path recursive"

	Get-ChildItem $path -Include ".svn" -Force -Recurse |
		Where {$_.psIsContainer -eq $true} |
		Foreach ($_)
		{
			Remove-Item $_.Fullname -Force -Recurse
		}
}

The Write-Host Cmdlet just writes the content to console window.

If you are like me, a PowerShell novice – start with the Getting Started with Windows PowerShell article and use the free tool PowerGUI from Quest Software. It’s PowerShell IDE with an integrated syntax highlighter editor and debugger.

In line 5 the Get-ChildItem Cmdlet iterates the path recursively and filtering the result to include only “.svn” files and folders. The force parameter allows the cmdlet to get items that cannot otherwise be accessed by the user, such as hidden or system files. Get-ChildItem Cmdlet can also iterate the registry.

Afterwards the result from Get-ChildItem Cmdlet is piped to the Where-Object Cmdlet (Where is an alias for Where-Object). The psIsContainer is a property on a folder. If it is equal to true pass it to the next pipe. I could have written the following instead:

Where {$_.mode -match "d"}

Use the below statement to list all properties for the files and folders in the current folder:

Get-ChildItem | format-list -property *

The foreach statement iterates every item and deletes the folder with the Remove-Item Cmdlet.

Calling the method is as simple as:

RemoveSvnFolders("c:\svn\My Solution")

On TechNet there is a myriad of articles with the root Windows PowerShell Core and more task oriented like A Task-Based Guide to Windows PowerShell Cmdlets and Piping and the Pipeline in Windows PowerShell.

Remove SVN folders PowerShell Script.

Happy PowerShelling…

No related posts.

Miracle Open World 2010 Lucene Presentation

2010-08-09T18:12:40Z

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.

Speaking about Lucene at Miracle Open World 2010

2010-04-04T17:04:10Z

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.

Upgraded the wireless network at work

2010-04-04T14:47:19Z

With new Wireless Access Points spread around the office building, we can now utilize the full potential of the 802.11n protocol.

No related posts.

It’s my birthday today

2010-04-02T07:07:17Z

Yep – It’s true, I’m one year older today.

The American singer and songwriter Marvin Gaye and Hans Christian Andersen – the world famous Danish author and poet noted for his children’s fairy tale stories like “The Ugly Duckling” and “The Little Mermaid” – both where born April 2^nd.

This is also the day, The Falklands War started where Argentina invades the British Falkland Islands in 1982.

You can read more about April 2^nd on Wikipedia.

Now I’m already 33 years old :-S

No related posts.

Compress files into individual archives

2010-03-21T13:20:29Z

I needed to compress a lot of files into individual zip archives – I did not want to do it manually

Add the following to a bat file and every file with the extension txt will be compressed into a Zip archive with 7-Zip file archiver:

@echo off
For %%f in (*.txt) do 7z.exe a -tzip %%f.zip %%f

E.g. a.txt will be compressed to the archive a.txt.zip

This was not exactly what I needed, as the dual extension caused problems in later processing. In needed to remove the extension preceding the zip extension – therefore:

@echo off
For %%f in (*.txt) do 7z.exe a -tzip %%~nf.zip %%f

E.g. a.txt will be compressed to the archive a.zip

That’s it

No related posts.

SQL Server build version

2010-04-30T08:34:32Z

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

Transferring SQL Server logins

2010-03-01T17:31:53Z

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

Cisco VPN (IPSec) support on 64 bit platforms

2010-05-31T18:08:34Z

I like Windows 7 x64, but I hate Cisco’s lack of support for the IPSec protocol on 64-bit platforms. Many of our customers use IPSec and the Cisco VPN Client – therefore I cannot connect to the customer’s network via IPSec VPN tunnels on my primary laptop

Until today

A colleague of mine recommended Shrew Soft VPN Client. It’s free and works like a charm. It’s a lot faster connecting and negotiating to the remote network than Cisco VPN Client, so fast in fact, that I initially thought that the connection failed. I’ve been using it for a couple of days, connecting to multiple customers, without any issues.

Why does Cisco implement a VPN client for x64 platforms?

I guess it is a money making scheme. They want to push their new Cisco VPN boxes and their new Cisco AnyConnect VPN client (expensive!), which makes use of SSL VPN.

Greg Ferro has another critical article Early Death of Cisco VPN Client Forces VPN License Fees with more details about Cisco’s SSL VPN.

I know of a commercial IPSec VPN client from NCP that works fine with Cisco IPSec VPN tunnels, but the steep price tag of $144 USD + taxes is too much.

Update May 31, 2010: Cisco has released an x64 version of their client tools for Windows 7 with IPSec protocol support. Either my money making scheme hypotheses is wrong or Cisco feared the wrath of my blog readers

Lucene.Net and Transactions

2010-08-09T18:13:13Z

Lucene.Net is an open source full text search engine library (a port from Java). It is stable and works like a charm – I’ve been using Lucene.Net for a couple of years now and implement a handful of solutions. Lucene is awesome.

If you want to try working with Lucene.Net, then the DimeCast.Net crew has recently made two short webcasts introducing Lucene.Net.

.Net 2.0 made it simple to use transactions with the System.Transactions namespace. Two of the great features are automatic elevation to distributed transactions (and utilize the Distributed Transaction Coordinator) and the other is the simplicity of creating your own transactional resource managers.

The .Net Framework defines a resource manager as a resource that can automatically enlist in a transaction managed by System.Transactions – which means that any object that implements any of the following interfaces can enlist in a transaction:

IEnlistmentNotification for the two-phase-commit protocol
IPromotableSinglePhaseNotification for the single-phase-commit protocol (non-distributed transactions)

To implement a resource manager for the Lucene.Net IndexWriter, and therefore make it transactional, all you have to do is the following:

public class TransactionalIndexWriter : IndexWriter, IEnlistmentNotification
{
    #region ctor
    public TransactionalIndexWriter(Directory d, Analyzer a, bool create, MaxFieldLength mfl)
        : base(d, a, create, mfl)
    {
        EnlistTransaction();
    }
    /* More constructors */
    #endregion

    public void EnlistTransaction()
    {
        // Enlist in transaction if ambient transaction exists
        Transaction tx = Transaction.Current;
        if (tx != null)
            tx.EnlistVolatile(this, EnlistmentOptions.None);
    }

    #region IEnlistmentNotification Members
    public void Commit(Enlistment enlistment)
    {
        base.Commit();
        enlistment.Done();
    }

    public void InDoubt(Enlistment enlistment)
    {
        // Do nothing.
        enlistment.Done();
    }

    public void Prepare(PreparingEnlistment preparingEnlistment)
    {
        base.PrepareCommit();
        preparingEnlistment.Prepared();
    }

    public void Rollback(Enlistment enlistment)
    {
        base.Rollback();
        enlistment.Done();
    }
    #endregion
}

You can use it like so:

IndexWriter indexWriter = null;
TransactionScope tx = null;

try
{
    tx = new TransactionScope();
    indexWriter = new TransactionalIndexWriter(...);

    // Perform transactional work
    indexWriter.AddDocument(new Document());
    indexWriter.AddDocument(new Document());
    indexWriter.AddDocument(new Document());

    // Connect to Database, MSMQ etc. to elevate to a distributed transaction

    // Commit transaction
    tx.Complete();
}
finally
{
    if (tx != null)
        tx.Dispose();

    if (indexWriter != null)
        indexWriter.Close();
}

Fairly simply uh? Just remember to instantiate the TransactionalIndexWriter or call the public method EnlistTransaction within the scope of an ambient transaction.
You might consider implementing IDisposable for TransactionalIndexWriter so you can take advantage of the using statement.

I will leave it to the reader to implement a TransactionalIndexReader.

If you want to try working with Lucene.Net, then the DimeCast.Net crew has recently made two 10 short webcast introducing Lucene.Net (http://dimecasts.net/Casts/ByTag/Lucene).

TechEd Berlin 2009

2009-11-06T20:21:47Z

I’m going to TechEd conference in Berlin next week. Are you going?

I haven’t taken the time to browse through the session catalogs yet, but I will be seeking information about:

Microsoft’s acquisition of FAST Search and how Microsoft incorporates into their products
Microsoft SQL Server 2008 R2 and especially the spatial support for Reporting Services
The new features of Windows Communication Foundation 4.0, the redesigned Windows Workflow Foundation 4.0 and .Net 4.0 in general.
The Windows Azure Platform
ASP.Net MVC 2

If you are going, drop me a mail and I’ll buy you a beer. That’s the least I can do for my readers

ASP.NET MVC Best Practices

2009-10-29T19:06:22Z

I love ASP.Net MVC – It has made web development fun. It also introduced new pitfalls…

Microsoft MVP Simone Chiaretta has fathered 12 ASP.NET MVC Best Practices worth reading.

In particular I find the these items interesting:

“DomainModel != ViewModel” with the ASP.NET MVC View Model Patterns
“Pay attention to the Verbs” with the PRG Pattern (Post-Redirect-Get)

Great conference

Initial slow WCF request

2009-10-23T21:01:06Z

If working with any of the HTTP Bindings you might experience that the first WCF request takes a long time to complete.

This is because the initial HTTP connection tries to get the proxy settings automatically. This is done by requesting the configuration via a HTTP GET http://wpad/wpad.dat. If proxy server automatic configuration is not configured, the request times out and the initial WCF can send the request directly to the destination address. This may add 30 seconds to the initial WCF request!

You can disable this behavior by specifying UseDefaultWebProxy = false on the binding.

You can read more about Web Proxy Auto-Discovery Protocol ( WPAD ) at Wikipedia.

This applies to basicHttpBinding, wsHttpBinding, wsDualHttpBinding, webHttpBinding, ws2007FederationHttpBinding, wsFederationHttpBinding, basicHttpContextBinding, wsHttpContextBinding and the new Azure ServiceBus bindings basicHttpRelayBinding, wsHttpRelayBinding, webHttpRelayBinding

Reuse in SQL Server 2008 Integration Services

2009-10-22T19:27:34Z

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

Monitors and thread context

2009-10-15T19:27:54Z

Running the below code will fail – why?


var syncRoot = new object();

Monitor.Enter(syncRoot);

ThreadPool.QueueUserWorkItem(x => Monitor.Exit(syncRoot));

It will throw a SynchronizationLockException with the message “Object synchronization method was called from an unsynchronized block of code.”

It is because System.Threading.Monitor requires the Enter and Exit methods must be executed on the same thread for the same synchronization object.

I did not know that :-/

New blog – NoTech

2009-10-14T06:24:34Z

A talented colleague of mine Thomas Schou-Moldt has started blogging about software development processes and methods. He is passionate about the subject and writes well.

The blog is in Danish and called NoTech.

Blog description:

Here I’ll share my opinions, joys and frustrations about software development and IT projects – both the good stories and horrendous experiences.

Amazon Virtual Private Cloud

2009-08-26T20:16:12Z

If you are interested in cloud computing, but you never known how to adopt this new paradigm in your shop due to ignorance I encourage you to look at the Amazon EC2 and related services.

One of the things that have kept me and properly many others from migrating our applications and services to the cloud has been due to the nature of most software requiring integration to legacy systems behind a company firewall.

Amazon’s new service – Amazon Virtual Private Cloud enables you to extend the company’s network to Amazon’s cloud via a secure VPN connection.

Read more about this new service from Amazon on Werner Vogels’ article Seamlessly Extending the Data Center – Introducing Amazon Virtual Private Cloud.

Configuring the DTC for WCF with OleTx

Change Data Capture (CDC) in SQL Server 2008

2009-08-11T13:38:53Z

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

40+ Essential Front End Web Developer Cheat Sheets

2009-06-23T06:32:21Z

tripwiremagazine.com has gathered a neat collection of cheat sheets for web developers.

I’m not a front end web developer, so I’m in desperate need of tools and cheat sheets that can help me look good, when venturing into the world of web design

Visual Studio 2010 Beta 1 available

2009-07-13T18:57:25Z

I can’t wait to see what Microsoft can get out of WPF with the new WPF-based IDE.

The Microsoft developer and platform evangelist Dan Fernandez has written an installation cheat sheet guide.

Update 13th July 2009: On the BCL Team Blog Justin Van Patten has written the blog post What’s New in the BCL in .NET 4 Beta 1.

How to view default values for a WCF binding

2010-08-29T06:25:38Z

… or create a custom binding from a build-in binding.
… or create an administrative XML-based configuration from an administrative programmatic configuration.

Below codes does all that:

// Specify the source binding
// - Programmatic binding
// - Administrative XML-based binding
// - Convert to custom binding

/* Programmatic binding */
var binding = new BasicHttpBinding();
binding.TransferMode = TransferMode.Streamed;
binding.MaxReceivedMessageSize = 10000;

/* Administrative XML-based binding */
// var binding = new BasicHttpBinding("basicHttp");

/* Convert to custom binding */
// var wsBinding = new WSHttpBinding("wsHttp");
// var binding = new CustomBinding(wsBinding);

string outputConfigFile = "out.config";

Configuration machineConfig = ConfigurationManager.OpenMachineConfiguration();

var fileMap = new ExeConfigurationFileMap();
fileMap.ExeConfigFilename = outputConfigFile;
fileMap.MachineConfigFilename = machineConfig.FilePath;

Configuration config = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);
config.NamespaceDeclared = true;

var scg = new ServiceContractGenerator(config);

string sectionName, configName;
scg.GenerateBinding(binding, out sectionName, out configName);
config.Save();

The programmatic source binding will create a configuration file with all default values for the BasicHttpBinding except for TransferMode and MaxReceivedMessageSize attributes like so:

<basichttpbinding>
    <binding name="BasicHttpBinding"
             closeTimeout="00:01:00"
             openTimeout="00:01:00"
             receiveTimeout="00:10:00"
             sendTimeout="00:01:00"
             allowCookies="false"
             bypassProxyOnLocal="false"
             hostNameComparisonMode="StrongWildcard"
             maxBufferSize="65536"
             maxBufferPoolSize="524288"
             maxReceivedMessageSize="10000"
             messageEncoding="Text"
             textEncoding="utf-8"
             transferMode="Streamed"
             useDefaultWebProxy="true">
        <readerquotas maxDepth="32"
                      maxStringContentLength="8192"
                      maxArrayLength="16384"
                      maxBytesPerRead="4096"
                      maxNameTableCharCount="16384" />
        <security mode="None">
            <transport clientCredentialType="None"
                       proxyCredentialType="None"
                       realm="" />
            <message clientCredentialType="UserName"
                     algorithmSuite="Default" />
        </security>
    </binding>
</basichttpbinding>

I found this tip by Brian McNamara on the MSDN WCF forum.

World’s first electronic annual general meeting

2009-04-16T12:02:16Z

Today I just witness the electronic annual general meeting for the investment company SparIndex. This is the first 100% electronic annual general meeting ever.

The stakes was high, especially for the system owner VP Securities as journalists and photographs wandered around the offices ready to report success or failure in the media.

My job as a technical consultant, together with a handful of other techies, was to monitor the solution during the live annual general meeting. Our job was to prevent any technical glitches or in the event of a crash recover as fast as possible.

The tension was high right up to the last minute before the premiere, but everything went smooth.

Screenshot of the World’s first electronic annual general meeting

Loooong running sql statement

2009-04-06T10:39:29Z

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!

Easy win – index candidates on SQL Server 2005

In-flight entertainment running on Windows

2009-03-24T10:33:13Z

Recently when I was flying back to Europe from South Africa with Lufthansa on Airbus A340-600 the In-flight entertainment crashed just before takeoff. They had to restart the entire system. I could see the entire reload of the system including the initial download with 115K baud XModem protocol download.

Yikes! I hope that only non-vital systems are running Windows for an airliner.

Scary SQL Server 2005 & 2008 bug

2009-03-28T14:27:28Z

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.

ASP.Net MVC 1.0 released

2009-03-18T21:02:12Z

I have been playing around with the ASP.NET MVC for a couple of month and cannot wait to start the first commercial project – hopefully next week

Frameworks like the ASP.NET MVC, MVCContrib and jQuery has sparked a renewed interest in web development, which has been dormant for years.

No related posts.

How to check for and enable read committed snapshot isolation

2009-03-18T11:51:14Z

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.

Handcrafting the WCF client

2009-03-08T15:58:47Z

Two years ago I wrote an article Building a Windows Communication Foundation client, describing three ways of creating WCF clients. I did not mention the fourth one, as I did not know at the time. Now I am older and wiser

The four ways of creating a WCF client are:

Visual Studio’s “Add Service” reference
Service Model Metadata Utility Tool (SvcUtil.exe) command line tool
Dynamic proxy with ChannelFactory
Handcrafting the WCF client

The last two options are only viable for WCF to WCF implementations where you either have control of both service and client or the service provider supplies a .Net assembly. Because both of them require the service contract aka the .Net interface marked with ServiceContractAttribute and OperationContractAttribute.

Anybody that has tried the “Add Service” reference in Visual Studio knows it is broken. First of all it generates an enormous amount of files, even for the simplest service contract.

Secondly it sometimes corrupts the state of the generated files, so you have to remove the reference and then add it again.. Just try to google it and you will find many frustrated developers. Do not use it!

The SvcUtil.exe is better and has a vast number of options. This is the preferred option for non .Net services or where the service contract interface is not available.

The dynamic proxy with ChannelFactory is useful, but be aware that the interface returned does not implement IDisposable, but the implementation does. See below:

var factory = new ChannelFactory<ihelloworldservice>("myEndPoint");

IHelloWorldService proxy = factory.CreateChannel();

using (proxy as IDisposable)
{
   MessageBox.Show(proxy.HelloWorld());
}

Finally the handcrafted version – the one I want to talk about. Writing the WCF client by hand is fairly easy – it requires the implementation to inherit from ClienBase and implementing the service contract interface. Below is first a simple service contract IHelloWorldService:

[ServiceContract(Namespace = "www.lybecker.com/blog/HelloWorldService")]
public interface IHelloWorldService
{
   [OperationContract]
   string HelloWorld();
}

Next the handcrafted WCF client with constructors and service contract interface implementation:

public class HelloWorldClient : ClientBase<ihelloworldservice>, IHelloWorldService
{
   public HelloWorldClient()
   { }

   public HelloWorldClient(string configurationName) : base(configurationName)
   { }

   public HelloWorldClient(Binding binding, EndpointAddress address) : base(binding, address)
   { }

   public string HelloWorld()
   {
      return Channel.HelloWorld();
   }
}

That’s it.

Now you have full control of the WCF client implementation.

Download the sample application Hello World WCF clients including a handcrafted version

Visiting Microsoft in Johannesburg, South Africa

2009-02-11T20:54:48Z

I live and work in the suburbs of Copenhagen in Denmark, but I’m currently in South Africa on business. While I’m here I might as well visit Microsoft South Africa – I did that today. I attended a presentation of the .Net services by Juval Löwy.

Microsoft South Africa is situated in a nice and fairly new building, as you can see on the picture above.

I was amazed about the level of security – first I had to cleared by the security guards before entering the parking lot. This is not uncommon in South Africa, but very uncommon in Denmark.

Inside in the reception the security check barriers prevent unauthorized people to enter.

When I finally was granted access to the presentation, Microsoft served sodas, pizza and beer!

Visual Studio 2008 shortcuts features

2009-02-02T16:18:08Z

Two years ago I wrote an article about the shortcut features in Visual Studio 2005. So I thought, why not write one for Visual Studio 2008.

I am a big fan of shortcuts – it increases productivity and ergonomically better than using the mouse all the time.

There is a large arsenal of shortcuts not only for Visual Studio, but also for the add-ins like ReSharper. Most developer forgets the basic ones in Windows besides the cut and paste shortcuts. But they do provide a huge productivity boost. Take a look at this list – how many of these shortcuts do you know and use?

SQL Server 2008 System Views poster

2009-02-02T16:17:40Z

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…

Easy win – index candidates on SQL Server 2005

2008-12-01T07:44:43Z

Below is my real life story telling how easy it can be to find index candidates if you use your tools.

This weekend we deployed a new version of a medium/large solution to the production environment for one of our customers. Normally this is not a big issue, as we are always prepared to roll back to the previous version – the one currently in production. The deployment and configuration processes are well-prepared and highly automated to minimize faults and late-night-bug-hunting-frenzy.

After the deployment is complete we smoke test the setup to make sure that it is configured correctly and all is sound.

This weekend after the successful deployment the smoke test failed miserably. We received TimeOutExceptions all over. It was coming from the user repository service.

We decided to investigate a little before the decision of aborting the deployment of the new version and therefore rolling back to the previous version.

The steps to resolve the issue is what I want to share with you.

A quick look in the source repository showed that almost no changes to the user repository service – none that could cause TimeOutExceptions.

With the SQL Server Profiler it was easy to spot the cause of the exception.

This simple select statement was isolated and copied into the Management Studio to view the Actual Execution Plan. The execution plan did show much, but by execution the below statement which instructs the SQL Server not to execute the Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.

SET SHOWPLAN_XML ON

The SQL Server will include loads in information and one of these are suggested indexes which you will not see with the graphical execution plan in Management Studio. Look for the MissingIndex elements:

<missingindexes>
  <missingindexgroup Impact="96.6222">
    <missingindex Database="[DOIPEI]" Schema="[dbo]" Table="[OrganisationalUnit_Stack]">
      <columngroup Usage="EQUALITY">
        <column Name="[org_pkid]" ColumnId="2" />
      </columngroup>
    </missingindex>
  </missingindexgroup>
</missingindexes>

By creating this single index on [DOIPEI].[dbo]. [OrganisationalUnit_Stack]. [org_pkid] the TimeOutExceptions disappeared, as the query no longer took minutes but milliseconds.

We were able to continue and successfully deploy the new version of the solution with only minimal disruption and a delay of only half an hour. Why the index where missing is still a mystery.

It makes all the difference in the world if you know the capabilities of your tools at hand.

.Net 3.5 installation WTF

2008-11-27T19:24:11Z

Installing .Net 3.5 SP1 can be difficult, especially when below screen appears

A colleague of mine stumbled upon this today. You cannot install SP1 because the SP1 install setup process is running

I just had to share it with you.

Serialization in .Net 3.5 SP1

2008-11-23T07:17:55Z

In .Net 3.5 SP1 it is now possible to serialize any object that has a default constructor without decorating it with [DataContract] or [Serializable].

Developers have these choices now when serializing objects:

Implicit serialization – requires public default constructor and only serialize public read/write fields
Use the [Serializable] attribute
Use [DataContract] and [DataMember] attributes
Implement the ISerializable interface

I must say, that I’m not keen on this new feature. I prefer explicit serialization to implicit serialization. I predict that implicit serialization of entire object graphs will cause performance problems for many .Net developers in the years to come. This could also be a good thing as I am a consultant

Aaron Skonnard has a more detailed blog post about the new serialization features in .Net 3.5 SP1.

SQL Server 2008 Microsoft ISV Partner events presentations

2008-11-15T07:23:32Z

This week I hosted two sessions at two different events for Microsoft Denmark. One at Radisson SAS Scandinavia Hotel in Århus and one in Microsoft Denmark’s headquarter in Hellerup.

It was fun at the feedback was very positive. The best part was during the breaks with great edifying technical discussions. All the slides are available at Miracle’s homepage.

.Net debugger visualizers

2008-10-12T15:35:28Z

The .Net framework 2.0 gave some great tools for debugging – they are a great help when writing code.

Some of these features are the DebuggerDisplay and DebuggerStepThrough attributes. I seldom use any of them, as I prefer to just overriding the ToString method instead of using the DebuggerDisplay attribute. This makes it easy to instrument the code too, as I can reuse the ToString method implementation for tracing.

Another feature is the ability to develop custom debugger visualizers. They enable smart developers to build their own. I find some really useful, especially the WCF visualizer. But there a many – check out this list of custom .Net debugger visualizers.

These small enhancements make it so easy to get an overview of the current state, instead of inspecting properties.

Disable and enable all database constraints

2009-01-03T17:41:01Z

We (my colleagues at Miracle and I) are currently in the process of upgrading a customer’s solutions. One of task is migrating 100 gigs of data into an existing database schema. This can easily become a tedious task and can take considerable calendar time. Therefore we decided to disable all constraints on the database, move the data and then enable them again, as we know the data integrity is sound.
We could have written a script that gets all the constraints in the database and generates a SQL script, but there is an easier way…
-- Disable all Constraints exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

--Enable all Constraints exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

That’s easy…

I found this on the Internet somewhere – the sp_MSforeachtable procedure is nowhere to be found in books online.
You can check if foreign keys and check constrains are disabled via the sys.foreign_keys and sys.check_constraints views.

Another obvious triviality in our situation is disabling and enabling triggers – that is easy too:

-- Disable all Triggers exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

-- Enable all Triggers exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

How to check for and enable read committed snapshot isolation

WCF certified

2008-10-17T19:17:46Z

I just got word from Microsoft, that I passed the Microsoft Certified Technology Specialist: .NET Framework 3.5, Windows Communication Foundation Applications certification (70-503).

It was not easy, as the preparation guide was incomplete and I didn’t have time to properly prepare for the test. I took the beta exam in January, but had totally forgotten about it.

I am proud to be one of the first to pass this exam

32-bit WCF memory allocation trouble

2008-10-05T10:30:21Z

With a 32 bit platform a process can only address 2 GB of memory by default on windows. For most applications this is not an issue. But today I really messed up – OOM (OutOfMemoryException) exceptions all over

I added loads of test data to our 32 bit development servers and tried to run a development utility. It failed and failed and failed with OOM exceptions. The cause was actually quite simple as the application tried to allocate more than 500 MB of consecutive memory.

The utility is a console app and the search index is a WCF server and they communicate via TCP buffered binary format (netTCPBinding).

The console utility retrieved all the unique identifiers in the search index (based on Lucene.Net) to compare these with the records in the SQL Server, to see if everything is in sync. The number of unique identifiers where now in the millions, but that was not the root cause. The process consumed twice the memory, because I use buffered transfer mode instead of streamed.

Buffered transfer mode serializes everything before sending the message from the server to the client. So in effect our WCF server not only had the huge array of unique identifiers but also a serialized version. It never got the stage where it could send the message.

Moral of the story – use streamed transfer mode with large messages.

As this is a development utility, I sidestepped

I enabled the 3GB switch allowing processes to allocate up to 3 GB of memory, and tried again. Same result: OOM.

I figured that the .Net might have a limitation and perhaps a switch to enable larger memory allocation. Apparently there is a small program called Microsoft COFF Binary File Editor (EDITBIN.EXE) that modifies Common Object File Format (COFF) binary files. This binary file editor can enable applications to address more than 2 GB of memory with the LARGEADDRESSAWARE option.

The steps to enable LARGEADDRESSAWARE for an application:

Start Visual Studio Command Prompt or run VSVARS32.BAT (in the visual studio subdirectory: Common7\Tools) which in effect does the same things.
Run editbin /LARGEADDRESSAWARE <yourApp>.exe

Tomorrow I will change the implementation to use streamed transfer mode. Promise

Do you know how many types there are in the .Net Framework?

2008-10-05T10:29:42Z

The answer is not 42 , but 11.417. I will never be able to grasp the vast capabilities of the .Net Framework.

With more than 100.000 member it’s no wonder that I am addicted IntelliSense.

You can get more details on Brad Abrams blog entry.

My presentation at the Microsoft product launch 2008

2008-10-05T10:29:23Z

All the presentations from the product launch are recorded as webcasts. They are available here (it’s in Danish). Later this year the webcast will be moved to TechNet.

I spoke about long overdue features in SQL Server for developers.

See the webcast
Download the presentation and demo

Some of the webcasts has poor sound quality (not mine ), which is a shame.

The webcasts where recorded at Microsoft. They used Camtasia Studio which I highly recommend as it is easy, intuitive and have some great features.

I guess the poor sound quality is due to lack of proper microphones and the recording was done in a regular meeting room. They used the build-in microphone on my laptop. Microsoft Denmark should have better sound equipment and a recording studio if they are serious about webcasts.

Microsoft product launch 2008

2008-10-05T10:28:55Z

I am speaking at the launch of Windows Server 2008, Visual Studio 2008 and SQL Server 2008 in Denmark February 28th. It will be an online launch – meaning that you can watch in comfort of your personal development sphere

As it is a Danish event and we Danes speak Danish – the event will be in Danish

You can sign-up here.