SQL Server Transact Basic to Expert - Set New Language to be used in Current Session in SQL
This blog shows how to set the new language to be used for the session in SQL Server
Example:
SET LANGUAGE 'Espanol'
Now select the language and check
SET @@LANGUAGE Lang
The expected result would be Espanol