Writing User-Defined Messages

The requirement to globalize the PT application presents a challenge to the database designers to make application messages available in all of the target languages (U.S. English, German, and Japanese) and to provide a way to easily extend the application to additional languages. The design team has two solutions:

• Add a table to the Eval database and store the PT message strings in a table column.
• Add the user-defined PT messages to the sysmessages table in the master database.

The design team decides to extend the built-in messaging functionality of SQL Server for the following reasons:

• User-defined messages in the sysmessages table are part of the SQL Server error reporting.
• The structure of the sysmessages table already supports multiple entries for the same message (one for each target language).
• The master database offers a central place for all database error messages across the organization.

About the sysmessages Table

The following illustration shows the column properties of the sysmessages table.

• The error column contains the error number of the message. User-defined messages begin at the number 50000. Error numbers below 50000 are reserved for use by SQL Server.
• The severity column contains the severity level of the message. Most of the PT user-defined messages have a severity level of 10, indicating the problem is caused by mistakes in the information the user has entered.
• The dlevel column is reserved for internal use. User-defined messages are assigned a dlevel of 0 and this value should not be changed.
• The description column contains the text of the message. Notice that this column contains the nvarchar Unicode data type, which allows you to enter characters from any character set.
• The msglangid column contains the system message group ID. When you create or edit a user-defined message you can choose a language and the msglangid column stores the identifier of the language.

A Sample PT Error Message

The sysmessages table has a constraint on the combination of the error, dlevel, and msglangid columns. This allows the table to store one message (with the same error number) in each of the 24 languages available in SQL Server. The following illustration shows an example of one user-defined message with a value of 50002 in the error column and three values in the mslangid column: 1031 for German, 1033 for English, and 1041 for Japanese.