August 16, 2010 at 10:09 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy