How to: Create and Use Localized Strings

Article
09/22/2010

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

This tutorial demonstrates how to localize strings within the SQL Server Modeling Services. The SQL Server Modeling Services uses the System.Globalization schema to support localization. You will learn how to use this schema to store and retrieve localized strings.

To create a table with a [System.Globalization].[StringId] column

On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.
In the Connect to Server dialog, type the name of the SQL Server Modeling Services server in the Server name combo box.
Specify the Authentication properties, and then click the Connect button.
On the toolbar, click the New Query button. A blank query window should open.

Insert the following T-SQL statements into the query window.

use Repository
go

-- Create a schema.
create schema [Location] authorization [RepositoryOwner]
go

-- Create a table with a localize string column
-- of type [System.Globalization].[StringId].
create table [Location].[CountriesTable](
   [Id] bigint not null identity,
   [CountryName] [System.Globalization].[StringId] not null,
)
go

Press the F5 key to run the T-SQL query. This query creates a Location schema that owns a CountriesTable table. This table defines a column named CountryName of type [System.Globalization].[StringId]. This column holds the identifier for one or more localized versions of a country’s name.

To create and use localized string instances

Before adding rows to the [Location].[CountriesTable] table, you must first create a unique string for the [System.Globalization].[StringId] column. The easiest solution is to create and store a GUID as a string in this field. In this example, the StringId in the script is '474DCEE3-240E-4134-A594-32BB4ED2CA99'. Use this StringId to insert the localized strings into the [System.Globalization].[Strings] view.

In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statements.

-- Create a new [System.Globalization].[StringId],
-- which is a uniqueidentifier (GUID).
declare @countryNameId as [System.Globalization].[StringId] = '474DCEE3-240E-4134-A594-32BB4ED2CA99'

-- Insert localized versions of the country name
-- into the [System.Globalization].[Strings] view.
insert [System.Globalization].[Strings] ([Id], [Folder], [Locale], [String])
   select @countryNameId, 1, N'en', N'United States'
   union
   select @countryNameId, 1, N'fr-FR', N'Etats-Unis'

-- Use the [System.Globalization].[StringId] in the original table.
insert [Location].[CountriesTable] ([CountryName])
   values (@countryNameId)
go

Press the F5 key to run the query. The [Location].[CountriesTable] table now has one row with a [System.Globalization].[StringId] value for the CountryName column. The [System.Globalization].[Strings] view has two rows for the same StringId. The first row registers the string United States for the locale en or English-Neutral. The second row registers the string Etats-Unis for the locale fr-FR or France-French.
In the query window, replace the previous text with the following T-SQL statement.
```
select * from [Location].[CountriesTable]
go
```
Press the F5 key to run the query. Note the row in the [Location].[CountriesTable] table.

To look up localized strings by an explicit locale

After adding [System.Globalization].[StringId] values to the CountryName column, the next step is to retrieve the correct localized string. For example, consider an application that uses the [Location].[CountriesTable] table. Or consider an Modeling Services-aware tool that explores this table. In both cases, English and French users would benefit from seeing the actual string from the [System.Globalization].[Strings] view in their own locales. There are several ways for applications, tools, and direct queries to obtain the localized strings.

In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statements.
```
-- Obtain a localized version of the string explicitly.
select Id, CountryName,
   [System.Globalization].[LocalesString](CountryName, 1, 'en-US') as LocalizedCountryName
from [Location].[CountriesTable]
```
Press the F5 key to run the query. This query uses the [System.Globalization].[LocalesString] function to explicitly request the localized strings for the CountryName column in the en-US locale.

Note

Note that the English version of the string was actually registered with the English-Neutral locale, en. When you specify a specific locale, such as en-US, the System.Globalization functions also search for the language-neutral version of that locale, such as en.

To look up localized strings by an implicit locale

In addition to explicitly specifying the desired locale, you can also implicitly search for localized strings using the locale of your current database session. In the next several steps, the SET LANGUAGE statement is used to simulate different database sessions for both the French and English locales.

In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statements.
```
-- Simulate the French locale with the SET LANGUAGE statement.
SET LANGUAGE French
select Id, CountryName, 
   [System.Globalization].[SessionsString](CountryName, 1) as CountryNameString
from [Location].[CountriesTable]
```
Press the F5 key to run the query. This query uses the [System.Globalization].[SessionsString] function to implicitly request the localized strings for the CountryName column. Unlike the [System.Globalization].[LocalesString] function, the [System.Globalization].[SessionsString] function does not have a parameter for the requested locale. Instead, the locale is deduced from the language of the current database session. The SET LANGUAGE statement changed the database session language to French. This results in the [System.Globalization].[SessionsString] function returning the French version of the country name.

In the query window, replace the previous text with the following T-SQL statements.

SET LANGUAGE English
select Id, CountryName, 
   [System.Globalization].[SessionsString](CountryName, 1) as CountryNameString
from [Location].[CountriesTable]
go

Press the F5 key to run the query. This time the SET LANGUAGE statement changes the database session language to United States English. This results in the [System.Globalization].[SessionsString] function returning the English version of the country name.

Share via

How to: Create and Use Localized Strings

To create a table with a [System.Globalization].[StringId] column

To create and use localized string instances

To look up localized strings by an explicit locale

To look up localized strings by an implicit locale

See Also

Concepts

Additional resources