November 28, 2010

Change the Default Language for SQL Server




I sometimes get questions like – How do I change the default language of SQL Server 2005/2008 from English to Spanish, so as to display the SQL Server system messages in that language.

The answer is that if you have installed a Localized version of SQL Server (say in Spanish), then you get the system messages in two languages – US English and Spanish. In this case, you just have to make sure that the Default Language selected is Spanish.

There are 3 common ways to do so: Using SSMS, T-SQL and PowerShell. We will use SSMS and T-SQL in this article.

USING SSMS

To do so, open SQL Server Management Studio (SSMS) > Right click Server in Object Explorer > Properties > Advanced > Check the ‘Default Language’ property and make sure it is set to Spanish.

image

Select the language from the DropDown and click OK. Then Stop and Start SQL Server for the new language settings to take effect.

USING T-SQL

If you want to change the default language of SQL Server to a language of your choice using T-SQL, follow these steps:

1. Look up the sys.messages catalog view to check if SQL Server supports a message in your local language.

SELECT msg.language_id, lang.langid, alias 
FROM
sys.messages AS msg
JOIN
syslanguages AS lang
ON lang.msglangid = msg.language_id
GROUP BY msg.language_id, lang.langid, alias

image

You get only 12 rows, which means SQL Server supports system messages and user-defined messages in only 12 local languages. (FEEDBACK: SQL Server should support more local languages!! By the way, check the sp_addmessage which offers some respite)

2. Once you know the LanguageID for your local language, for Spanish it is 5, just use this command to change the default language of SQL Server

EXEC sp_configure "default language", 5
RECONFIGURE WITH OVERRIDE

Start and Stop SQL Server. You can now verify if the local language has changed using the following command

sp_configure 'default language'

image

If the config_value shows 5, then you are good to go!

Alternatively, if you do not have a localized version of SQL Server, then you can use the SELECT @@language command

SET Language Spanish
SELECT @@language, @@langid

Strangely, if you do not explicitly say ‘SET Language Spanish’ and just run the @@language, @@langid command, the message still shows US_English, even if you have followed the steps I had shown above. That’s why i have explicitly used SET Language Spanish option.

Update: Please note that the contents of this post is valid if you have installed a Localized version of SQL Server, in our case Spanish.

If you have not installed the localized version of SQL Server, the default language is US English. If you need to change the default language on this machine, then you will have to change the default language for individual logins, as doing it on a server level won't work. Also as Geri Reshef points out in the comments section, you will have to use SET LANGUAGE to change the language to Spanish.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

10 Responses to "Change the Default Language for SQL Server"
  1. גרי רשף said...
    November 28, 2010 at 9:02 PM

    Changing the default language of the server will not change the language of the open queries in the SSMS: the language of the query is defined by the default language of the Login, and you can change it (the language of the already opened query) using Set Language command.

  2. Suprotim Agarwal said...
    November 28, 2010 at 9:49 PM

    @גרי רשף - I am not very sure what you meant. As I mentioned at the beginning of the post, the user wanted to display the SQL Server system messages/user messages in a local language (Spanish). The method I have explained here shows that!

    Please elaborate on the context of your comment.

  3. גרי רשף said...
    November 28, 2010 at 10:07 PM

    Suppose you run the command:
    Select DateName(DW,GetDate());
    You will probably get 'Monday' which is the English name of the current day of week (this message was published in 29/11/2010).
    If you'll change the default language of the server to Spanish- you'll still get 'Monday' and not 'Lunes' unless you change the language of the current query using Set Language command.

  4. Suprotim Agarwal said...
    November 28, 2010 at 11:00 PM

    גרי רשף - I think I understand your point now.

    At the beginning of this post, I had mentioned the following line: if you have installed a Localized version of SQL Server (say in Spanish). I have highlighted it now in the post.

    So the steps I have mentioned works if a local version of SQL Server is installed, but for some reason, the default language was not set to it - eg: Spanish. The emphasis was that on a localised SQL box, system messages are available in two languages - English and the Local Language. To set it to the local language, follow the steps shown.

    I have added an update at the end of the post to clarify things further and removed the confusing line.

    Thanks for your comment as it helped me clarify things! By the way, why does your name show as גרי רשף . What's your name in English? :)

  5. גרי רשף said...
    November 28, 2010 at 11:29 PM

    Thanks you- I think I understand now bettter the subject.

    Well, my name is Geri Reshef, and I guess the system took it as I wrote it in Hebrew from my GMail settings.. I'll try to correct it!

    As you can see- my previous comment was written in 28/11/2010 22:00 when our local time was 29/11/2010 08:00..

  6. Suprotim Agarwal said...
    November 28, 2010 at 11:40 PM

    No problem Geri. Thanks again for your comments and I hope you will be visiting my blog in the future too!

  7. Navsingh from Intellect Banking said...
    December 1, 2010 at 10:49 PM

    Nice... any other queries for changing language?

  8. Azeem said...
    March 9, 2011 at 12:52 PM

    I have one query.
    My friend has Microsoft SQL Server installed in Russian. I need to manage it for a while as he will be out of town. How can I change the language to English? The thing is all the menus are displayed in Russian. so you will have to tell me precisely how to change the language.
    Thanks and regards,
    Azeem

  9. sarah lee said...
    February 11, 2014 at 5:22 PM

    I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you have made in writing this article.


    enola
    www.edupdf.org

  10. Leslie Lim said...
    April 9, 2014 at 6:49 PM

    Your blog is absolutely worth to read if anybody comes throughout it. I'm lucky I did because now I've got a whole new view of this. I didn't realize that this issue was so important and so universal. You certainly put it in perspective for me.


    Utada
    www.imarksweb.org

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions