Article ID: 199037 - View products that this article applies to.
This article was previously published under Q199037
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.0SQL 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.
http://msdn2.microsoft.com/en-us/library/ms188674.aspxThe 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.5SQL 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:
-T3602Note 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 2005To 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:
Article ID: 199037 - Last Review: February 22, 2007 - Revision: 4.3