Lesson Learned #16: How to change the default language of Azure SQL Database


Hello Everyone,

We got several questions how is possible to change the default language using server level ( trough sp_configure ) or login definition.

 

Option 1: Change the default language using 'sp_configure' 

  • Unfortunately, there is not possible to configure a server level the default language , if you try to change it running EXEC sp_configure 'default language', 'British English', you will receive the error message: Msg 2812, Level 16, State 62, Line 2 - Could not find stored procedure 'sp_configure'.

 

Option 2: Change the default language of the login. 

  • Also, there is not possible to change the default language or default database per login, for example, if you try to execute the TSQL command in master, CREATE LOGIN test WITH PASSWORD = 'PwdComplex123!', DEFAULT_LANGUAGE = British English you will receive the message:  Msg 40517, Level 16, State 1, Line 1 -  Keyword or statement option 'default_language' is not supported in this version of SQL Server.

 

Our workaround

  • You need to specify in every connection the default language that you need. You could change in two ways:
    • Once the connection has been stablished using the SET LANGUAGE 'British English' See this URL: https://msdn.microsoft.com/en-us/library/ms174398.aspx.
      • If you need to know how many languages are supported and the definition of them, execute the query exec sp_helplanguage
      • Also, you have available other settings at connection level as SET DATEFORMAT or SET DATEFIRST.
    • In terms of application, you have the following parameter in the connection string called Language , adding this parameter in the connection string "data source=tcp:servername.database.windows.net,1433;initial catalog=DDBBName;User ID=username;Password=ComplexPwd!;ConnectRetryCount=3;ConnectRetryInterval=10;Connection Timeout=30;Language=British English

 

Additional Information

Comments (1)

  1. Rick says:

    This is utterly horrible, Having language set permanently to US_English is definitely not the way to go, especially when it comes to date formats, America, please realise that there is one (yes, just one!!) country in the world that uses the MDY format. Nearly all the others use the much more sensible DMY format and having to state this for every connection is just a nightmare. This is something that definitely needs fixing, why not simply go the whole hog and just store dates in YYYY-MM-DD HH:MM:SS and have done with it?

Skip to main content