Appending data to a sql text or ntext column

Today I came accross a problem where nvarchar data got larger than the maximum that can be stored in a record. I had to convert to a ntext column and append the data to it.

I quickly found out that I needed to use the UPDATETEXT function in combination with the TEXTPTR and DATALENGTH functions.

When you are working with the ntext type then the output of DATALENGTH must be divided by two to get the string length as it returns the length of the data in bytes.

DECLARE @TextToAppend nvarchar(max)
SET @TextToAppend = 'Append this piece of text'

DECLARE @pointer binary(16)
DECLARE @length int

	@pointer = TEXTPTR(MyLargeTextColumn)
	,@length = DATALENGTH(MyLargeTextColumn)/2 -- Needed for ntext
	ID = @ID

UPDATETEXT MyTableWithLargeTextColumn.MyLargeTextColumn @pointer @length 0 @TextToAppend


The above SQL code appends the text @TextToAppend to column MyTableWithLargeTextColumn.MyLargeTextColumn for the record with @ID.

Simplify string resources management for localization

Every dotnet developer will eventually do something with localization. That is ‘working with multiple languages for presentation’. This post is not about solving lay-out related stuff which is about creating a good UI design which allows for RTL and LTR languages and variable with controls but about how to manage all that language data.

Use a global language

First you have to choose the main language used in your application. A lot of developer start with using their native language and add other translations. My native language is Dutch. If I wrote an application and want to translate it to for example Chinese. I admit that I should have knowledge about the Chinese language but it is a bit lacking. So often you want to get it translated by someone else. I think it is much easier to find somebody that can translate from English to different flavors of Chinese then from Dutch. So that would require me to first translate all Dutch literals to English. Second, if you want to stimulate people to work on your code as is often a requirement for a successful open-source project then you should use an accessible language in your code which is probably English too.

Storing multi-lingual literals

Microsoft thought about this and their solution for .net is the use of *.resx files. Each language has its own *.resx file and the way the designer works is that it creates a *.resx file per form/page. Maintaining these files for two or three languages for a small amount of forms or pages can be done manually but when the project becomes larger and literals are added, removed, updated or deleted then this becomes a burden.

You can create your own variation to store this. For example storing these literals in a format that has no references with the code or store everything in a database. A major drawback for any of these solutions is that it requires all developers to know how it works, you wont have designer support and it also needs to be as performing as the .net solution.

So it is best to just your the Microsoft way and remove its drawback which is all about the management of all those resx files. 

Resx management

So how can we maintain all those files. Visual Studio 2008 still doesn’t provide good support for this but luckily other people experienced the same problems and provide a solution.

Synchronizing structure

The first thing is about synchronizing resx files. This is where ResxSync is a very nice tool. It is a command-line driven tool. It is not meant for maintaining your resx files content. It is about keeping your resx files in sync.

So why not do this with a editing tool? Well synchronizing is something you can do automated on for example a build server and because its XML you can easily perform checks on those synchronized files and add the results of such a scan in your build server results.

If you don’t have a build server then the other tools are of more use to you.


Then we still need to do the translation. Translating by hand requires you to open multiple resx files in your editor of choice. Visual Studio has a nice grid like editor but it really is a b***h to have multiple files opened. They are not in sync while editing so it takes too much time to translate.

There are two tools that I found that provide a similar editing experience are the Zeta Resource Editor and ResEx.


  • In Zeta you select multiple resx files that belong together and it loads it in one grid. ResEx is somewhat smarter because you only need to open the ‘base’ resx and it automatically loads all translations.
  • Zeta has project support and can load multiple base resx files simultaneously.
  • Resx can hide fields which are already translated.
  • Resx has an option to add a new culture with ease.

I find Resx easier to work with as it has a simple and fast open base resx, translate missing values and close approach. Both tools miss the option to open a visual studio project file to easily edit the resx from from there. What probably would be even more cool would be that Resx be a editor within visual studio.

So my winner (for the moment) is ResEx.

Where to get those cool tools

Zeta Resource Editor:



Enso is now part of my must have list

First of all what is Enso? Enso is an application launcher and utility tool that uses the selected text as input and replaces it with its output (if necessary). Its a bit of a descriptive one liner and I think its better to navigate to the humanized website and look-up what it is about.

I am now using it for almost one week and it really is a must have tool that I now using each and every day. Its that fast and intuitive that I feel that its productive. The whole select text and perform action approach that it uses is just too brilliant that you really get the feeling like “Why didn’t we have such a tool years ago?”.

Nevermind.. just try it for a few days. You will definately keep it installed. If you are a keyboard fanatic that is.


  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta