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