Wednesday, September 19, 2012

Getting OS locale settings from SQL Server

Recently I had a requirement to identify the language settings of windows operating system from SQL Server. The purpose behind this was to resolve the format in which date values were send by a client program executing from the server.
I came across a new DMV which helps us in identifying the operating system version and locale ID from SQL Server.

The DMV is sys.dm_os_windows_info and gives us the below info

As you see from the above, we will be able to get the version detail, service pack level, sku and language version for the windows os running in the server.
The version number can be looked up in the below article to get details on the os version

http://msdn.microsoft.com/en-us/library/windows/desktop/ms724832(v=vs.85).aspx

The locale ID can be used to retrieve the language details using the below article

http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx
The above screenshot indicates that the server is currently running the os Windows 7 with language setting of  English - United States
This can come handy in similar scenarios where we need to identify locale/language settings of the server and implement some logic based on that.
Please keep in mind that this DMV is available only from SQL 2008 R2 version with SP1