DDL_DATABASE_LEVEL_EVENTS scope question

• Scott H

  Old Hand

  Points: 332

  More actions

  August 16, 2010 at 10:09 am

  #98889

  Hi all,

  I've been developing a DDL trigger for administrative reporting on one of our test servers, with a view to expanding it across the entire SQL server estate, however what I thought was working on Friday no longer does, and I'm getting a rather confusing error message.

  Here's a simplified version of the code I'm trying to run.

  [font="Courier New"]CREATE TRIGGER DDLLoggingTrigger

  ON ALL SERVER

  FOR DDL_DATABASE_LEVEL_EVENTS[/b]

  AS

  BEGIN

  SET NOCOUNT ON

  -- Declare variables, populate from EventData(), check logging table exists (recreate if not, then add record to alert on drop), add entry to logging table

  SET NOCOUNT OFF

  END[/font]

  Now the message I'm getting is as follows -

  Msg 1098, Level 15, State 1, Procedure DDLLoggingTrigger, Line 5

  The specified event type(s) is/are not valid on the specified target object.

  Almost every single website I've checked shows the 'DDL_DATABASE_LEVEL_EVENTS' group being used at a DATABASE level, however the MSDN documentation suggests that this can be picked up from both a Server and DB scope trigger and I would swear blind that I got this working at a Server level on Friday, prior to some server patching over the weekend (I'm still waiting on the Sysadmins to tell me exactly which patches were deployed).

  Am I going mad, or can this DDL event group actually be used at an 'ALL SERVER' level as part of a trigger, and if so what am I doing wrong to implement it?

  Thanks in advance.