SQL Server Performance Forum – Threads Archive

Need to Log Error description in error log table

Hi, I am trying to keep a log of SQL errors in an error log table I created in my database. One thing that I do is retrieve the error description from the sysmessages table. For example, I can use the following query to retrieve the SQL error message: SELECT [Description]
FROM Master.SysMessages
WHERE Error = @@Error An example of a result that I can get is: %ls statement conflicted with %ls %ls constraint ‘%.*ls’. The conflict occurred in database ‘%.*ls’, table ‘%.*ls’%ls%.*ls%ls. SQL Server, however, replaces the placeholders (%ls) with more specific information: INSERT statement conflicted with COLUMN FOREIGN KEY constraint ‘FK_ExampleFK’. The conflict occurred in database ‘ExampleDB’, table ‘Example’, column ‘ExampleID’. My question is, how can I capture the error message with specific information, rather than the general error message (with placeholders) contained in the sysmessages system table in the master database? I appreciate the help Thanks, Itika
itika

One option is:#
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. If not look in Books online for @@ERROR topic which is suitable for your requirement. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>