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