Change the Default Language for SQL Server

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.

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 
FROMsys.messages AS msg
JOINsyslanguages AS lang
ON lang.msglangid = msg.language_id
GROUP BY  msg.language_id, lang.langid, alias

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'

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.