How to capture error messages that are sent to clients from an instance of SQL Server

Trapping error messages and warning messages that are sent to a client from the SQL Server can provide powerful information when you are tracking down a problem.

This document explains, in detail, how to accomplish this from Microsoft SQL Server 6.5, Microsoft SQL Server 7.0, and Microsoft SQL Server 2005

SQL Server 7.0

SQL Server 7.0 contains the SQL Server Profiler and enhanced features to capture error and warning messages directly. To create a trace to capture error messages raised by SQL Server, follow these steps.

Notes

• In SQL Server 6.5, the SQL Server Profileris called SQL Trce.
• Some commands, such as the raiserror command, are not traced as exceptions. You may have to watch the Stmt object to trap raiserror information.

1. Start Profiler.
2. On the Tools menu, click Options.
3. Select the Trace All Event Types and Trace All Data columns.
4. Save your changes.
5. Create a new trace or edit an existing trace.
6. On the Events tab, add the Exception event from the Error and Warning group.
7. On the Data Columns tab, add the Integer Data to the selected data.
8. Save your changes.

Note If you are using SQL Server 2005, you can use this method to capture error messages. However, the user interface in SQL Server 2005 may differ from the user interface in SQL Server 7.0. For more information about how to use SQL Server Profile to specify an event in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web site:The Exception type is raised when the SQL Server detects an error or warning. The Integer Data column will contain the actual message number. You can easily test the filter by running the following:This will raise the error 208, "object not found" error message to the client. The SQL Profiler trace will show the error 208 as the exception.

Note Know that the Exception type can be raised and not returned to the client. Many areas of the SQL Server code use the exception logic to handle expected errors.

For example, if you try to select from a table that does not exist, the error 208 is raised to the client. Two instances of the internal exception 200 are also raised, but are never seen by the client. However, these exceptions are raised to the Profiler event model and seen in the trace output.

You can reduce the number of exceptions recorded by Profiler by limiting the filter to specific clients and looking for a severity value < 25. All user-defined error messages are defined as severity 24 or less. (Edit your trace and set the Maximum value for the severity to 24 in the Filters tab.)

SQL Server 6.5

SQL Server version 6.5 uses a series of trace flags to enable the capture functionality.

Use the SQL Server Setup program to add the following two startup parameters:Note Trace flags should be used on a limited basis because they can generate large output.

Trace flag 3602 records all error and warning messages sent to the client. Trace flag 3605 forces the information to be put in the SQL Server error log. See your SQL Server installation log directory for the error log files such as C:\Mssql\Log. You cannot reduce the number of exceptions raised in SQL Server 6.5.

If you want to add the incoming client statements to the log, enable the -T4032 as a third startup parameter.

SQL Server 2005

To capture error messages in SQL Server 2005, use the method that is described for SQL Server 7.0.

Note The user interface in SQL Server 2005 may differ from the user interface in SQL Server 7.0. For more information about how to use SQL Server Profile to specify an event in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web site: