This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysmessages returns one row for every error or warning message within SQL Server.  Each discrete error or warning message is in the compatibility view multiple times with a translation of each message for each supported language.

Sysmessages is replaced by the catalog view sys.messages.  The output from sys.messages is quite similar to sysmessages and returns one row for every message in SQL Server with a translation for supported languages, as well.

Status Column

The sysmessages compatibility view does not contain a status column, but it does have a column named dlevel, which functions in the same manner as a status would.  The column contains a single bit value that identifies whether the error or warning is logged to the SQL Server event log.  The bit value used is 0×80, or 128.

Query Via sysmessages

With the simplicity of the dlevel column, queries against sysmessages are fairly simple.  Besides the expression for that column, the other four columns in the compatibility view are returned as is.  A sample query against sysmessages is provided in Listing 1.

--Listing 1 – Query for sys.sysmessages
SELECT error
,severity
,dlevel
,CONVERT(INT,dlevel & 0x80) / 128 AS is_event_logged
,description
,msglangid
FROM sysmessages

Query via sys.messages

As easy as the query is for the compatibility view, the query against the catalog view sys.messages is more simple.  The dlevel column is not included in the catalog view, instead the value is represented directly in the column is_event_logged.  The only other difference is the renaming of a few columns.  The query in Listing 2 provides query against the catalog view that covers all of the columns from the compatibility view.

--Listing 2 – Query for sys.messages
SELECT message_id AS error
,severity
,is_event_logged
,text AS description
,language_id AS msglangid
FROM sys.messages

Summary

In this post, we compared the compatibility view sysmessages with the catalog view sys.messages.  Modifying applications or processes to use the supported catalog view over the compatibility view is a simple feat; which you will hopefully be open to partaking in.  After reading all of this, do you see any reason to continue using sysmessages?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – sysindexkeys
2. Lost in Translation – Deprecated System Tables – sysmembers
3. Lost in Translation – Deprecated System Tables – sysperfinfo