none
Write event log with T-sql?

    Question

  • Hi,

     

    Is there a way to write event log using transact-sql? For example, I can specify eventId, eventType, eventDescription. I find RAISERROR and sys.messages can write event, but I can't control the eventId and eventType. Also the event description is formatted in a sql server way, like 'Error: 50000 Severity: 1 State: 2 ....'. Do I have to write a dll that can be loaded in sql server to achieve this?

     

    Fan,

    Thanks!

    Friday, February 01, 2008 1:04 PM

Answers

  • I can't say definatively, but I believe that the answer is no you can't change the ID number.  If you have 2005, a simple SQLCLR procedure could be created to wrap around the EventLog.  Something like

     

     

    Code Snippet

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

     

    public partial class StoredProcedures

    {

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static SqlBoolean WriteEventLog(

    SqlString message, SqlInt32 EventID)

    {

    System.Diagnostics.EventLog el = new System.Diagnostics.EventLog("Application");

    el.WriteEntry(message, System.Diagnostics.EventLogEntryType.Error, EventID);

    }

    }

     

     

    Friday, February 01, 2008 1:44 PM
    Moderator

All replies

  • show the exetended procedure xp_logevent

    HTH

    Friday, February 01, 2008 1:22 PM
  • You need to specify the with log option on Raiserror.

    Code Snippet

    declare @msg varchar(1000)

    set @msg = 'A Problem Occured and was logged.'

    RAISERROR (@Msg, 16, 1) WITH LOG

     

     

    You get two entries in the SQL Log that bubble up to the Event Log

     

    Code Snippet

    Date  2/1/2008 8:20:10 AM
    Log  SQL Server (Current - 2/1/2008 8:20:00 AM)

    Source  spid51

    Message
    Error: 50000, Severity: 16, State: 1.

     


     

    Date  2/1/2008 8:20:10 AM
    Log  SQL Server (Current - 2/1/2008 8:20:00 AM)

    Source  spid51

    Message
    A Problem Occured and was logged.

     

     

     

    Code Snippet

    Event Type: Error
    Event Source: MSSQL$DEV02
    Event Category: (2)
    Event ID: 17063
    Date:  2/1/2008
    Time:  8:20:10 AM
    User:  domain\username
    Computer: MyServer
    Description:
    Error: 50000 Severity: 16 State: 1

    A Problem Occured and was logged.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 50 c3 00 00 10 00 00 00   PÃ......
    0008: 0f 00 00 00 4f 00 53 00   ....O.S.
    0010: 48 00 4f 00 44 00 42 00   H.O.D.B.
    0018: 30 00 35 00 5c 00 44 00   0.5.\.D.
    0020: 45 00 56 00 30 00 32 00   E.V.0.2.
    0028: 00 00 0b 00 00 00 64 00   ......d.
    0030: 62 00 43 00 6f 00 6e 00   b.C.o.n.
    0038: 74 00 61 00 63 00 74 00   t.a.c.t.
    0040: 73 00 00 00               s...   

     

     


    Friday, February 01, 2008 1:24 PM
    Moderator
  • Thanks for the quick reply! I see the event Id is always like 'Event ID: 17063'. Can I change the ID number? And does 17063 always mean user defined event log?

    Friday, February 01, 2008 1:31 PM
  • I can't say definatively, but I believe that the answer is no you can't change the ID number.  If you have 2005, a simple SQLCLR procedure could be created to wrap around the EventLog.  Something like

     

     

    Code Snippet

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

     

    public partial class StoredProcedures

    {

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static SqlBoolean WriteEventLog(

    SqlString message, SqlInt32 EventID)

    {

    System.Diagnostics.EventLog el = new System.Diagnostics.EventLog("Application");

    el.WriteEntry(message, System.Diagnostics.EventLogEntryType.Error, EventID);

    }

    }

     

     

    Friday, February 01, 2008 1:44 PM
    Moderator