Thursday, September 07, 2006
How to view error messages in sys.messages with SSMS
Currently, in the RTM and SP1 versions of SQL Server Management Studio (SSMS), there is no GUI way to view sys.messages (formerly sysmessages) that are stored in the master database. If you want to view the messages, you have two choices - one "old" and one "new". The "old" way is to keep writing this code:
Anyway, I far prefer the new catalog view to the old sysmessages table query simply because everything is right there for you to see - the language, the alert logging info, etc. Here's a helpful query to show you the various languages that SQL Server stores (or can store) error messages:
SELECT *
FROM sys.messages m JOIN sys.syslanguages l
ON m.language_id = l.msglangid
WHERE m.message_id = 3043
I find it strange that it is language_id in sys.messages, the new catalog view, but it is msglangid in sys.syslanguages (also a new catalog view). Oh well!
SELECT * FROM master.dbo.sysmessagesThe "new" way is to use the catalog view "sys.messages":
SELECT * FROM sys.messages; -- no need to write master.sys.messagesIn SQL Server 2000 and 7.0's Enterprise Manager, we could easily view the messages with the GUI. While it wasn't way I preferred to do it, the one advantage was that you could easily see whether or not the error message was logged to the Windows Event Log. The catalog view in SQL Server 2005 gives us the is_event_logged column for this information.
Anyway, I far prefer the new catalog view to the old sysmessages table query simply because everything is right there for you to see - the language, the alert logging info, etc. Here's a helpful query to show you the various languages that SQL Server stores (or can store) error messages:
SELECT *
FROM sys.messages m JOIN sys.syslanguages l
ON m.language_id = l.msglangid
WHERE m.message_id = 3043
I find it strange that it is language_id in sys.messages, the new catalog view, but it is msglangid in sys.syslanguages (also a new catalog view). Oh well!
Technorati Tags: Error, SQL Error, SQL Server Error, SQL Server 2005, SQL Server, Microsoft SQL Server
