Recently I was asked how to export events from a SQL Server 2005 and send them to the Event Viewer Log, so that the SCOM administrator could see these events.

As I found out, there existed no “out of the box” solution for SQL Server 2005. The workaround we found was to develop our own solution:

·         At first you need to capture a profiler trace:

o    The profiler trace should be saved inside a database table and not inside a file.

o    You should use appropriate filters to avoid capturing unnecessary events

 e.g. filter just for events with TEXTDATA like '%update%', if you want to capture only UPDATE queries

You don’t necessarily need the Profiler Tool to capture a trace. You can configure, start and stop a profiler trace programmatically by using stored procedures like:

sp_trace_setstatus

http://msdn.microsoft.com/en-us/library/ms176034(SQL.90).aspx

sp_trace_setevent

http://msdn.microsoft.com/en-us/library/ms186265(SQL.90).aspx

sp_trace_setfilter

http://msdn.microsoft.com/en-us/library/ms174404(SQL.90).aspx

You could even go as far as creating your own unique events:

sp_trace_generateevent

http://msdn.microsoft.com/en-us/library/ms177548(SQL.90).aspx

·         Then you need to create a trigger on this table (or maybe create a SQL Server scheduled job that is executed periodically).

o    For any new event that is logged inside this table, you can then use the XP_LOGEVENT stored procedure to log a user-defined message in the Windows Event Viewer.

for Reference:

xp_logevent (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms186244(SQL.90).aspx

NOTE

In SQL Server 2008 there is a new feature called Extended Events, which you can use to send specific SQL Events to the Windows Event Viewer:

Using SQL Server Extended Events

http://msdn.microsoft.com/en-us/library/bb630317.aspx

SQL Server Extended Events How-to Topics

http://msdn.microsoft.com/en-us/library/bb630340.aspx