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...
>
>
|||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...[vbcol=seagreen]
> 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 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...
>
>

DDL Server Trigger Error (SQL2005)

I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's comin
g
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...
>
>|||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...[vbcol=seagreen]
> 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 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...
>
>