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.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

8 comments:

גרי רשף Geri Reshef said...

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.

Suprotim Agarwal said...

@גרי רשף - 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.

גרי רשף Geri Reshef said...

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.

Suprotim Agarwal said...

גרי רשף - 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? :)

גרי רשף Geri Reshef said...

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..

Suprotim Agarwal said...

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

Navsingh from Intellect Banking said...

Nice... any other queries for changing language?

Azeem said...

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