INF: Trapping Error Messages Sent to Clients from a SQL Server

ID: Q199037

The information in this article applies to:

Microsoft SQL Server versions 6.5, 7.0

SUMMARY

The opportunity to trap error and warning messages 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 both SQL Server version 6.5 and 7.0.

MORE INFORMATION

SQL SERVER 7.0

SQL Server 7.0 contains the SQL Server Profiler (in SQL Server 6.5, this is called SQL Trace) and enhanced features to capture error and warning messages directly. Use the steps below to create a trace to capture error messages raised by SQL Server.

NOTE: Some commands like raiserror are not traced as exceptions. You may need to watch the Stmt object to trap raiserror information.

Start Profiler.

On the Tools menu, click Options.

Select the Trace All Event Types and Trace All Data columns.

Save your changes.

Create a new trace or edit an existing trace.

On the Events tab, add the Exception event from the Error and Warning group.

On the Data Columns tab, add the Integer Data to the selected data.

Save your changes.

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:


select * from master..authors

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: Be aware that the Exception type can be raised and not returned to the client. Many places in the SQL Server code use the exception logic to handle expected errors.

For example, if you attempt 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.

It is possible to 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:

-T3602
-T3605

CAUTION: 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 placed in the SQL Server error log. Refer to your SQL Server installation log directory for the error log files (for example, C:\Mssql\Log). There is no way to reduce the number of exceptions raised in SQL Server 6.5.

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

Additional query words: prodsql err errs msg errorlog sql server profiler

Keywords : SSrvGen
Version : winnt:6.5,7.0
Platform : winnt
Issue type : kbhowto kbinfo