DDL_DATABASE_LEVEL_EVENTS scope question

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

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply