Today I want to talk about some of the language settings in SQL Server and the impact they have. Read on to see about setting up a default language in SQL Server, setting language for a scoped session, and differences between SQL Server and Windows Server settings.
Configuring Default Language in SQL Server
Language in SQL Server is an instance level configuration. Microsoft has some documentation about this: Configure the default language Server Configuration Option
A server restart is NOT required after changing this configuration.
Executing sp_helplanguage will show general info about languages.
--Call for ALL languages exec sp_helplanguage; --Call for Russian info exec sp_helplanguage russian;
Change Language in SSMS
Directions from SSMS: Connect to instance –> Right click instance in Object Browser –> Properties –> Advanced tab –> Default Language
NOTE: The Microsoft documentation is slightly off as it suggests to change under the general tab and not the advanced tab.
Changing it to Italian we can see the default language change via:
--default language select * from sys.configurations where [name] like '%language%'; --lookup in syslanguages select * from sys.syslanguages; --combine them select * from sys.configurations c inner join sys.syslanguages lang on c.[value] = lang.langid where c.[name] like '%language%';
Selecting from sys.syslanguages returns info about each language available in the SQL instance.
Change Language in T-SQL
Simple open a thread to the instance and, from any database, execute:
EXEC sp_configure 'default language', 2; --change default to French GO RECONFIGURE; GO
NOTE: changing the default language in SQL Server will NOT affect the scope of your query threads (i.e. tabs in SSMS).
Changing the default language will be marked in the SQL Server error log as such:
Setting a Session Scoped Language in SQL Server
Here is how to see the current language for a session via @@LANGUAGE:
--Returns the name of the language currently being used select @@language;
The SET LANGUAGE command allows us to choose a language for a session. By session here I mean by SPID. Each query tab you open in SSMS is another thread to the database and receives a SPID. This can be called by almost anyone who has permissions to access the database because it only requires membership in the public role to execute.
Now let us change the session language to Russian.
SET LANGUAGE Russian;
I think it means the language settings are now Russian. We can verify the change by querying:
From this point out all of the messages that SQL Server returns to you in the query message or errors will be Russian.
select 1/0; --dividing by zero to throw an error
Here is another demonstration with dates:
DECLARE @Today DATETIME; SET @Today = getdate(); SET LANGUAGE Russian; SELECT DATENAME(month, @Today) AS 'Month Name'; SET LANGUAGE us_english; SELECT DATENAME(month, @Today) AS 'Month Name' ; GO
My Russian is not that good so let’s change it back. Simply set the language as demonstrated above back to English and all is well again.
OS Language is Not the Same Thing as SQL Server Language
If the OS keyboard is set to Russian then it will use Russian characters when you type. The error messages, log entries, and everything else will still be English. It affects only your typing. Go ahead and change the language, open notepad or SSMS, and start typing. The letters you hit will not be what you expect.
Thanks for reading!
If you liked this post then you might also like: Microsoft SQL Server Environmental Checks