Changing SQL Server 2005 language

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Changing SQL Server 2005 language

Author

Topic

nmg196
Yak Posting Veteran

70 Posts

Posted - 2006-03-08 : 05:58:24

Hi,

I have just installed SQL Server 2005 Developer Edition on our development server, but under the properties, it says "Language (United States)". How can I change this setting? I am in the UK and I wish it to have United Kingdom defaults. We have had this problem in the past and we even have some software which physically won't install if the SQL Server locale does not match the operating system locale.

Can somebody please explain where this option is configured in SQL 2005.

Also, is it possible to install SQL Server Management Studio on my workstation? I installed the client utilities but it only seemed to install SQL Server Configuration Manager.

Thanks,

Nick...

Tahsin
Starting Member

34 Posts

Posted - 2006-03-08 : 11:50:03

I don't think that's the location but, rather, the language property. It's simply stating that your default language is us_english. You can change the language to British (British English) if you want.

Run the following:

EXEC sp_configure 'default language', 23 
RECONFIGURE

Run to verify:

SELECT @@language, @@langid

nmg196
Yak Posting Veteran

70 Posts

Posted - 2006-03-08 : 11:57:49

If I run SELECT @@language, @@langid it says "us_english" even after running sp_configure 'default language', 23.

According to sys.syslanguages, 23 *IS* British English. But the server still reports it's running US English.

It also still seems to want US date format for queries. Surely theres a way to change this?!

Tahsin
Starting Member

34 Posts

Posted - 2006-03-08 : 14:54:46

You're right ... that's so weird. I changed it on my server and it shows what you just said. Something else that is weird is that when you run sp_configure without any parameters, it shows that the default language has changed to 23, so I am not sure what's going on.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-08 : 16:19:29

Did you run the RECONFIGURE command?

You can read about RECONFIGURE in SQL Server Books Online.

CODO ERGO SUM

nmg196
Yak Posting Veteran

70 Posts

Posted - 2006-03-08 : 17:03:26

Yes, but running it doesn't seem to make any difference. It still seems impossible to change the default language.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-08 : 17:32:09

It sounds like you forgot to change the default lanquage for each login.

CODO ERGO SUM

nmg196
Yak Posting Veteran

70 Posts

Posted - 2006-03-08 : 18:29:00

I've changed it for sa, and as it's a new server that's pretty much the only login.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-08 : 19:01:15

Does that mean it is OK now?

If not, what does this show you?


select @@LANGUAGE

select name, language from master.dbo.syslogins

exec sp_configure 'default language'

CODO ERGO SUM

nmg196
Yak Posting Veteran

70 Posts

Posted - 2006-03-09 : 04:38:07

SELECT @@language, @@langid
gives:
us_english 0

exec sp_configure 'default language'
gives:
default language 0 9999 23 23

So it appears to be British English, but the server still shows it as being US English on the properties page as shown in my screenshot above. The server still expects dates to be in US format.

I think the commmand above only changes the default language for logins - not the main setting for SQL Server itself (ie the value shown on my screenshot).

Tahsin
Starting Member

34 Posts

Posted - 2006-03-09 : 10:15:35

To change the default language of a login, you can either use sp_defaultlanguage or

ALTER LOGIN sa WITH DEFAULT_LANGUAGE = British;
GO

You can check the default language of your logins by going to Management Studio -> Security -> Logins and right clicking on sa and checking the default language

- Tahsin

nmg196
Yak Posting Veteran

70 Posts

Posted - 2006-03-10 : 05:03:02

I have received a reply from Microsoft that says that SQL Server (when installed on a British English machine) is locked down to US English and there's no way to change it to use UK English. You can change it for individual logins, but I've found this doesn't seem to work all the time. Great...

dmcmoran
Starting Member

4 Posts

Posted - 2006-07-08 : 21:34:17

I don't know if any of you guys have managed to get SQL 2005 Express running as UK English, but I have. NO JOKE.

If your interested in knowing how, please post and I'll write it up. I'd do it now but it involves a reg hack and I need my bed!

mma
Starting Member

2 Posts

Posted - 2006-07-26 : 06:30:33

I would greatly appreciate know kno wyou got it working on SQL Server Express. I am having all the same dramas mentioned below in being able to use local date formats for the Australian machine ( uses british date formats)

Thanks in advance
Matt

quote:
Originally posted by dmcmoran

I don't know if any of you guys have managed to get SQL 2005 Express running as UK English, but I have. NO JOKE.

If your interested in knowing how, please post and I'll write it up. I'd do it now but it involves a reg hack and I need my bed!

dmcmoran
Starting Member

4 Posts

Posted - 2006-07-28 : 18:08:44

First off: BACKUP YOUR SERVER/REGISTRY before proceding with this hack, especially if it's a production box!

I take NO responsibility for any outages, server failures or any other problems that may arise from this hack. As is stated elsewhere in this topic, Microsoft say it can't be done (although I'm pretty sure they know it can!) so Microsoft WILL NOT provide technical support for any problems.

Right. There are 3 keys that need to be changed to ensure the SQL server and clients connect with your default language.

1.Navigate to http://msdn2.microsoft.com/en-us/library/ms190303.aspx

Find your language (use the codes under Windows LCID), e.g. for British English use code 2057.

2.You may want to stop the SQL services. I didn't but my server is a rag-tag beta box that is rebuilt once every week!

Open regedit, and find the following 3 keys:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

In these 3 keys you'll find the language settings. It's DWORD so when you edit the key, change to Decimal and put your language code in.

3.Once done, either restart the SQL services or restart the server (if possible). Start management studio and under 'YOURSQLSERVER' properties you should now see the Language you want.

mma
Starting Member

2 Posts

Posted - 2006-08-09 : 05:14:08

Thanks very much for your help. The hack worked well.

mr_point
Starting Member

1 Post

Posted - 2007-10-18 : 05:25:43

Hi Everyone, just to let you know regards the above hack that it may also be necessary to change the language property of one further registry key, that being:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\CurrentVersion]

Exactly the same way as detailed above. It took me doing that to finally resolve my language issues, but ta for the lead!

~LS

stkaye
Starting Member

1 Post

Posted - 2008-07-31 : 10:16:36

Have you not tried

SET Language British

This still wont change the value in the properties page but if you now run

select @@Language, @@Langid

you will get

British 23

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources