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!

Answer 1

show the exetended procedure xp_logevent

HTH

Answer 2

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...   

 

 

Answer 3

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?

Answer 4

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);

}

}