How to programmatically determine the default SQL Server collation for a Windows locale?

Question

Post reply

How to programmatically determine the default SQL Server collation for a Windows locale?

m.ogden

SSC Veteran

Points: 295
More actions
August 26, 2010 at 5:34 am

#387664

I work on a SQL Express 2008 R2 based desktop application that is sold to users in many countries. When our software is installed, the collation setting for our SQL Express instance is set to Latin1_General_BIN.
We have a business requirement to allow users to perform case insensitive searches on some of the data, with case conversions calculated based on the default SQL Server collation for the user's current Windows locale (to avoid the well known "Turkish I's" problem).
In other words, if the user's current Windows locale is "English (Australia)", we want to run SQL like:
SELECT ... WHERE field LIKE '%value%' COLLATE Latin1_General_CI_AS
while if the user's current Windows locale is "Turkish (Turkey)", we want to run SQL like:
SELECT ... WHERE field LIKE '%value%' COLLATE Turkish_CI_AS
My question is, is there a way (in T-SQL or .NET) to programmatically determine the default SQL Server collation for a given Windows locale? I'm looking for a function where I can pass in the Windows locale (or LCID) and get back the name of the default SQL Server collation - i.e. a programmatic version of some of the data on the following webpage:
http://msdn.microsoft.com/en-us/library/ms143508.aspx
Alternatively, is there an easier way to do what I'm trying to do?
(N.B. the T-SQL fn_helpcollations() function is not what I'm looking for, as this provides a one-to-one mapping between SQL Server LCIDs and SQL Server collations, whereas I'm looking for a many-to-one mapping between Windows LCIDs and SQL Server collations. Applying different collation settings for the whole SQL Express instance at install time is not the solution I'm after either.)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323574 More actions · Answer 1

you want to read the windows registry to get the collation before the installation, i think?

I'm only sure of how to get it afterwards....maybe you could run a script to change these settings after the install?

/*

--results:

CollationSQL_Latin1_General_CP1_CI_ASSQL_Latin1_General_CP1_CI_AS

*/

SELECT

'Collation', SERVERPROPERTY('Collation') As ServerCollation,

databasepropertyEx('Sandbox','Collation') As DataBaseCollation

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

If I understand correctly, you need to get the locale information from the local user (ie on the client computer) and pass it to SQL Server for it to be resolved to a collation. Therefore you need a .NET (or similar) routine to run at the client, and a table to be created on SQL Server to hold all the information in the table on the web page you mentioned.

Hope that helps

John

m.ogden SSC Veteran Points: 295 More actions · Answer 3

Lowell (8/26/2010)
you want to read the windows registry to get the collation before the installation, i think?
I'm only sure of how to get it afterwards....maybe you could run a script to change these settings after the install?
/*
--results:
CollationSQL_Latin1_General_CP1_CI_ASSQL_Latin1_General_CP1_CI_AS
*/
SELECT
'Collation', SERVERPROPERTY('Collation') As ServerCollation,
databasepropertyEx('Sandbox','Collation') As DataBaseCollation

I believe this code just gets the collation of the SQL Server instance (and the current database), which I already know is Latin1_General_BIN. Not what I'm after I'm afraid.

m.ogden SSC Veteran Points: 295 More actions · Answer 4

John Mitchell-245523 (8/26/2010)
If I understand correctly, you need to get the locale information from the local user (ie on the client computer) and pass it to SQL Server for it to be resolved to a collation. Therefore you need a .NET (or similar) routine to run at the client, and a table to be created on SQL Server to hold all the information in the table on the web page you mentioned.
Hope that helps
John

Thanks John - so you're saying you're not aware of there being any existing .NET or T-SQL function for this, and I have to build the lookup table myself. I can do that of course, but I was hoping there was already a canned conversion function out there.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I don't know of one, although I've never seen a requirement such as this before, so I suppose it's possible there is one.

John