SQL Server Performance

Need to Log Error description in error log table

Discussion in 'Analysis Services/Data Warehousing' started by itika, Jan 20, 2005.

  1. itika New Member

    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
  2. satya Moderator

    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.

Share This Page