Sunday, October 3, 2010

Sometimes it is important to choose right collation for the server

The title looks like a joke, am I right? Sometimes we just take care of collation for specific database. Well that may lead you to unpleasant situation and moreover getting wrong or no data at all. Recently I helped my friend to solve the problem for his new software. He created a database with "right" collation let say Hebrew_CI_AS and the program searches for the data that contains hebrew characters. Suddenly the program does not return the data at all. He showed me that the data he is looking for are presented in the table and point me to the stored procedure that performs the searching. You have already guessed what was the problem, right? The problem was because withing a stored procedure he used a temporary table where the programm stores the data for final result (VARCHAR(n) for search string) for some logic. As you know tempdb is a system database which inherits a server's collation (SQL_Latin1_General_CP1_CI_AS) and hence no data were returned. In that specific case we changed the data type to NVARCHAR(n) and there is no need to reinstall the entire server, but think about the program(I have seen many of such) that such technique (store unicode character in temporary tables) are used in hundred of stored procedures and change the datatype for all of them is very big headache.

3 comments:

Nike shox clearance said...

This is a great post,which has a lot of readers.
Such kind of post giving us various knowaledge,it worthy to read.It is so wonderful.

Our company provides Nike Shox Clearance rapid and safe, free shipping network marketing.

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!

Ojanx said...

I have really enjoyed reading your blog posts. Peluang Bisnis, Bisnis Pulsa, Iklan Gratis, Pasang Iklan Baris, Iklan Baris Gratis