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

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

dblib support in SQL 2005?

Hi there,

We have a server application that's built on DBLIB. From what I can tell, it runs on SQL2005, but I'm looking for official confirmation that it's supported. I recall seeing a document recently that said it was supported, but that there would be no documentation released for the API set any longer.

Anyone have any links to official information?

Thanks.

The following is copied from an internal email. It should answer your question. I have no official link for this except http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dblibc/dbc_pdc00b_589v.asp

Just to answer any lingering questions.
- ntwdblib.dll never shipped with the OS.
- ntwdblib.dll did ship with SQLServer up through SQLServer 2005. It is now deprecated.
- sqlserver 2005 supports down level to three versions. So you can use a dblib (ntwdblid.dll) based application to connect to sqlserver 2005.
- ntwdblib.dll never shipped a 64bit version.
Please make sure the ISV understands that they should be actively porting this application over to odbc or oledb.

|||

Here is the official doc.

http://msdn2.microsoft.com/en-us/library/ms143729(en-US,SQL.90).aspx

Although the SQL Server 2005 Database Engine still supports connections from existing applications using the DB-Library and Embedded SQL APIs, it does not include the files or documentation needed to do programming work on applications that use these APIs. A future version of the SQL Server Database Engine will drop support for connections from DB-Library or Embedded SQL applications. Do not use DB-Library or Embedded SQL to develop new applications. Remove any dependencies on either DB-Library or Embedded SQL when modifying existing applications. Instead of these APIs, use the SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2005 does not include the DB-Library DLL required to run these applications. To run DB-Library or Embedded SQL applications you must have available the DB-Library DLL from SQL Server version 6.5, SQL Server 7.0, or SQL Server 2000.