DDL Server Trigger Error (SQL2005)

I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's coming
up with the following error:
-----
Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
-----
I've checked on the internet and have noted someone else having the same
problem, but with no resolution. Is this a bug? Has anyone a work-around?
Thanks.Hi
Can you post DDL to be tested?
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> coming
> up with the following error:
> -----
> Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> registration.
> -----
> I've checked on the internet and have noted someone else having the same
> problem, but with no resolution. Is this a bug? Has anyone a work-around?
> Thanks.
>
>|||Hi Uri,
Here's the code (some bits removed for security):
CREATE TRIGGER trAll_ddl_server
ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
AS
DECLARE @.eventData xml, @.eventID int
SET @.eventData = eventdata()
INSERT <AuditTbl> ( AuditEventObject, PostTime,
spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
VALUES ('trAll_ddl_server',
cast(@.eventdata.query('data(//PostTime)') AS sysname),
cast(@.eventdata.query('data(//spid)') AS sysname),
cast(@.eventdata.query('data(//LoginName)') AS sysname),
cast(@.eventdata.query('data(//EventType)') AS sysname),
cast(@.eventdata.query('data(//ServerName)') AS sysname),
cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
cast(@.eventdata.query('data(//SchemaName)') AS sysname),
cast(@.eventdata.query('data(//ObjectName)') AS sysname),
cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
cast(@.eventdata.query('data(//ObjectType)') AS sysname),
cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
cast(@.eventdata.query('data(//EventData)') AS xml)
)
BEGIN
PRINT 'Permission denied. please contact the DBA.'
ROLLBACK
-- Set RolledBack flag
END
GO
/*
DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
DROP TRIGGER trAll_ddl_server ON ALL SERVER
*/
"Uri Dimant" wrote:
> Hi
> Can you post DDL to be tested?
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> > I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> > coming
> > up with the following error:
> > -----
> > Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> > "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> > registration.
> > -----
> >
> > I've checked on the internet and have noted someone else having the same
> > problem, but with no resolution. Is this a bug? Has anyone a work-around?
> >
> > Thanks.
> >
> >
> >
>
>|||Sorry, I've got the error
Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
Incorrect syntax near '<'.
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...
> Hi Uri,
> Here's the code (some bits removed for security):
> CREATE TRIGGER trAll_ddl_server
> ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
> AS
> DECLARE @.eventData xml, @.eventID int
> SET @.eventData = eventdata()
> INSERT <AuditTbl> ( AuditEventObject, PostTime,
> spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
> ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
> VALUES ('trAll_ddl_server',
> cast(@.eventdata.query('data(//PostTime)') AS sysname),
> cast(@.eventdata.query('data(//spid)') AS sysname),
> cast(@.eventdata.query('data(//LoginName)') AS sysname),
> cast(@.eventdata.query('data(//EventType)') AS sysname),
> cast(@.eventdata.query('data(//ServerName)') AS sysname),
> cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
> cast(@.eventdata.query('data(//SchemaName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectName)') AS sysname),
> cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectType)') AS sysname),
> cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
> cast(@.eventdata.query('data(//EventData)') AS xml)
> )
> BEGIN
> PRINT 'Permission denied. please contact the DBA.'
> ROLLBACK
> -- Set RolledBack flag
> END
> GO
> /*
> DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
> DROP TRIGGER trAll_ddl_server ON ALL SERVER
> */
>
>
> "Uri Dimant" wrote:
>> Hi
>> Can you post DDL to be tested?
>>
>> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
>> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
>> > I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
>> > coming
>> > up with the following error:
>> > -----
>> > Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
>> > "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
>> > registration.
>> > -----
>> >
>> > I've checked on the internet and have noted someone else having the
>> > same
>> > problem, but with no resolution. Is this a bug? Has anyone a
>> > work-around?
>> >
>> > Thanks.
>> >
>> >
>> >
>>|||Hi Uri,
I did say that some code had been taken out. Ok, here's some code that
should run as-is:
CREATE TRIGGER trig_All_ddl_database
ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
PRINT 'Database DDL Trigger Fired.'
GO
"Uri Dimant" wrote:
> Sorry, I've got the error
> Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
> Incorrect syntax near the keyword 'AS'.
> Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
> Incorrect syntax near '<'.
>
>
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...
> > Hi Uri,
> >
> > Here's the code (some bits removed for security):
> >
> > CREATE TRIGGER trAll_ddl_server
> > ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
> > AS
> >
> > DECLARE @.eventData xml, @.eventID int
> > SET @.eventData = eventdata()
> >
> > INSERT <AuditTbl> ( AuditEventObject, PostTime,
> > spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
> > ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
> > VALUES ('trAll_ddl_server',
> > cast(@.eventdata.query('data(//PostTime)') AS sysname),
> > cast(@.eventdata.query('data(//spid)') AS sysname),
> > cast(@.eventdata.query('data(//LoginName)') AS sysname),
> > cast(@.eventdata.query('data(//EventType)') AS sysname),
> > cast(@.eventdata.query('data(//ServerName)') AS sysname),
> > cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
> > cast(@.eventdata.query('data(//SchemaName)') AS sysname),
> > cast(@.eventdata.query('data(//ObjectName)') AS sysname),
> > cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
> > cast(@.eventdata.query('data(//ObjectType)') AS sysname),
> > cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
> > cast(@.eventdata.query('data(//EventData)') AS xml)
> > )
> >
> > BEGIN
> > PRINT 'Permission denied. please contact the DBA.'
> > ROLLBACK
> > -- Set RolledBack flag
> > END
> > GO
> >
> > /*
> > DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
> > DROP TRIGGER trAll_ddl_server ON ALL SERVER
> > */
> >
> >
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> Can you post DDL to be tested?
> >>
> >>
> >> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> >> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> >> > I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> >> > coming
> >> > up with the following error:
> >> > -----
> >> > Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> >> > "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> >> > registration.
> >> > -----
> >> >
> >> > I've checked on the internet and have noted someone else having the
> >> > same
> >> > problem, but with no resolution. Is this a bug? Has anyone a
> >> > work-around?
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>