Replication & DDL Triggers - any way to do it?

[M Bourgon]

I have been working on implementing DDL Triggers, to monitor changes
in our environment. However, it doesn't seem to behave with
Replication - at the very least, it chokes when new tables are added.
Does anyone have any suggestions or workarounds?

The only fix I've found is to disable/delete the trigger, let it
apply, then enable/create the trigger. Not an ideal solution.


Error messages:
Target string size is too small to represent the XML instance (Source:
MSSQLServer, Error number: 6354)
Get help: http://help/6354
Target string size is too small to represent the XML instance (Source:
MSSQLServer, Error number: 6354)
Get help: http://help/6354


Command attempted:
create procedure [sp_MSins_dbomyreplicatedtable]
@c1 int,@c2 char(11),@c3 char(13),@c4 char(11),@c5 char(11),@c6 char
(14),@c7 char(30),@c8 char(60),@c9 char(30),@c10 char(25),@c11 char
(1),@c12 int,@c13 char(1),@c14 decimal(19,5),@c15 char(11),@c16 char
(4),@c17 decimal(11,3),@c18 char(2),@c19 int,@c20 decimal(14,3),@c21
decimal(19,2),@c22 decimal(19,5),@c23 decimal(19,5),@c24 decimal
(19,5),@c25 char(1),@c26 char(1),@c27 char(1),@c28 char(2),@c29 char
(1),@c30 datetime,@c31 char(1),@c32 datetime,@c33 datetime,@c34 char
(Transaction sequence number: 0x00002CDC000002B6015B00000000, Command
ID: 13)

[unknown]

Did you find a solution to the conflict between DDL triggers on database and replication?



M Bourgon wrote:

Replication & DDL Triggers - any way to do it?
22-Jan-10

I have been working on implementing DDL Triggers, to monitor changes
in our environment. However, it does not seem to behave with
Replication - at the very least, it chokes when new tables are added.
Does anyone have any suggestions or workarounds?

The only fix I have found is to disable/delete the trigger, let it
apply, then enable/create the trigger. Not an ideal solution.


Error messages:
Target string size is too small to represent the XML instance (Source:
MSSQLServer, Error number: 6354)
Get help: http://help/6354
Target string size is too small to represent the XML instance (Source:
MSSQLServer, Error number: 6354)
Get help: http://help/6354


Command attempted:
create procedure [sp_MSins_dbomyreplicatedtable]
@c1 int,@c2 char(11),@c3 char(13),@c4 char(11),@c5 char(11),@c6 char
(14),@c7 char(30),@c8 char(60),@c9 char(30),@c10 char(25),@c11 char
(1),@c12 int,@c13 char(1),@c14 decimal(19,5),@c15 char(11),@c16 char
(4),@c17 decimal(11,3),@c18 char(2),@c19 int,@c20 decimal(14,3),@c21
decimal(19,2),@c22 decimal(19,5),@c23 decimal(19,5),@c24 decimal
(19,5),@c25 char(1),@c26 char(1),@c27 char(1),@c28 char(2),@c29 char
(1),@c30 datetime,@c31 char(1),@c32 datetime,@c33 datetime,@c34 char
(Transaction sequence number: 0x00002CDC000002B6015B00000000, Command
ID: 13)

Previous Posts In This Thread:

On Friday, January 22, 2010 1:47 PM
M Bourgon wrote:

Replication & DDL Triggers - any way to do it?
I have been working on implementing DDL Triggers, to monitor changes
in our environment. However, it does not seem to behave with
Replication - at the very least, it chokes when new tables are added.
Does anyone have any suggestions or workarounds?

The only fix I have found is to disable/delete the trigger, let it
apply, then enable/create the trigger. Not an ideal solution.


Error messages:
Target string size is too small to represent the XML instance (Source:
MSSQLServer, Error number: 6354)
Get help: http://help/6354
Target string size is too small to represent the XML instance (Source:
MSSQLServer, Error number: 6354)
Get help: http://help/6354


Command attempted:
create procedure [sp_MSins_dbomyreplicatedtable]
@c1 int,@c2 char(11),@c3 char(13),@c4 char(11),@c5 char(11),@c6 char
(14),@c7 char(30),@c8 char(60),@c9 char(30),@c10 char(25),@c11 char
(1),@c12 int,@c13 char(1),@c14 decimal(19,5),@c15 char(11),@c16 char
(4),@c17 decimal(11,3),@c18 char(2),@c19 int,@c20 decimal(14,3),@c21
decimal(19,2),@c22 decimal(19,5),@c23 decimal(19,5),@c24 decimal
(19,5),@c25 char(1),@c26 char(1),@c27 char(1),@c28 char(2),@c29 char
(1),@c30 datetime,@c31 char(1),@c32 datetime,@c33 datetime,@c34 char
(Transaction sequence number: 0x00002CDC000002B6015B00000000, Command
ID: 13)


Submitted via EggHeadCafe - Software Developer Portal of Choice
Entity Framework 4.0 POCO-First development and POCO Template
http://www.eggheadcafe.com/tutorials/aspnet/488279c4-214b-47ef-b152-955f3795ee13/entity-framework-40-pocofirst-development-and-poco-template.aspx

[Ben Thul]

My guess is that you have your DDL trigger logging the entire XML for
the event somewhere. How big is the column at the place where that
XML gets stored? I ask because I'm seeing the message "Target string
size is too small to represent the XML instance". It could also be
that you're not keeping the whole XML but are trying to put it into a
variable in your trigger that is too small; the result could be the
same. My point is that we have DDL triggers employed on all of our
databases and have never run into problems with them impeding any sort
of replication-related changes.
--
Ben

Post by unknown
Did you find a solution to the conflict between DDL triggers on database and replication?
Replication & DDL Triggers - any way to do it?
22-Jan-10
I have been working on implementing DDL Triggers, to monitor changes
in our environment.  However, it does not seem to behave with
Replication - at the very least, it chokes when new tables are added.
Does anyone have any suggestions or workarounds?
The only fix I have found is to disable/delete the trigger, let it
apply, then enable/create the trigger. Not an ideal solution.
MSSQLServer, Error number: 6354)
Get help:http://help/6354
MSSQLServer, Error number: 6354)
Get help:http://help/6354
create procedure [sp_MSins_dbomyreplicatedtable]
@c1 int,@c2 char(11),@c3 char(13),@c4 char(11),@c5 char(11),@c6 char
(Transaction sequence number: 0x00002CDC000002B6015B00000000, Command
ID: 13)
On Friday, January 22, 2010 1:47 PM
Replication & DDL Triggers - any way to do it?
I have been working on implementing DDL Triggers, to monitor changes
in our environment.  However, it does not seem to behave with
Replication - at the very least, it chokes when new tables are added.
Does anyone have any suggestions or workarounds?
The only fix I have found is to disable/delete the trigger, let it
apply, then enable/create the trigger. Not an ideal solution.
MSSQLServer, Error number: 6354)
Get help:http://help/6354
MSSQLServer, Error number: 6354)
Get help:http://help/6354
create procedure [sp_MSins_dbomyreplicatedtable]
@c1 int,@c2 char(11),@c3 char(13),@c4 char(11),@c5 char(11),@c6 char
(Transaction sequence number: 0x00002CDC000002B6015B00000000, Command
ID: 13)
Submitted via EggHeadCafe - Software Developer Portal of Choice
Entity Framework 4.0 POCO-First development and POCO Templatehttp://www.eggheadcafe.com/tutorials/aspnet/488279c4-214b-47ef-b152-9...

[M Bourgon]

Here's my full code. I have read that if you up the size of each
parameter and set ANSI_PADDING on it should work, but I came across
that this week and haven't had a chance to try it out. Here's my
code, blatantly cribbed from Dinesh Asanka on sql-server-
performance.com.

Looking at it fresh, I would change all the varchar fields to
nvarchar(255) or so, then narrowing them down. I originally thought
the problem was the Entire_Event_Data, but that's not it; it must be
weird results on some of the others.




/****** Object: DdlTrigger [ddltrg_Audit_Log] Script Date:
04/10/2010 01:17:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--1.1 version MDB 20091119. Removed the XML field as that's a lot of
data being held for no reason.
/* --creating the table that holds the details
use my_utils
If Object_ID('my_utils.dbo.DDL_Event_Log') IS NOT NULL
DROP TABLE dbo.DDL_Event_Log
CREATE TABLE dbo.DDL_Event_Log
(
ID int IDENTITY(1,1) NOT NULL,
EventTime datetime NULL,
EventType varchar(15) NULL,
LoginName VARCHAR(50),
ServerName varchar(25) NULL,
DatabaseName varchar(25) NULL,
ObjectType varchar(25) NULL,
ObjectName varchar(60) NULL,
UserName varchar(15) NULL,
CommandText varchar(max) NULL
--,Entire_Event_Data XML
)

*/

CREATE TRIGGER [ddltrg_Audit_Log] ON DATABASE -- Create Database DDL
Trigger
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_INDEX, DROP_INDEX, ALTER_INDEX,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_USER, ALTER_USER, DROP_USER
/*
CREATE TRIGGER ddltrg_Server_Audit_Log ON ALL SERVER -- Create
Database DDL Trigger
FOR
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
*/
AS
--http://www.sql-server-performance.com/articles/audit/
ddl_triggers_p1.aspx
--http://searchsqlserver.techtarget.com/tip/
0,289483,sid87_gci1346274,00.html for event types
--See http://msdn.microsoft.com/en-us/library/ms189871%28SQL.90%29.aspx
for event types
SET NOCOUNT ON
If Object_ID('my_utils.dbo.DDL_Event_Log') IS NOT NULL
BEGIN
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
-- Insert information to a Event_Log table
INSERT INTO my_utils.dbo.DDL_Event_Log
(
EventTime,
EventType,
LoginName,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
-- , Entire_Event_Data
)

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/
EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/
EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(50), @xmlEventData.query('data(/
EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(60), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/
EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/
EVENT_INSTANCE/TSQLCommand/CommandText)'))
-- , @xmlEventData
END

GO

[M Bourgon]

Here's my full code.  I have read that if you up the size of each
parameter and set ANSI_PADDING on it should work, but I came across
that this week and haven't had a chance to try it out.  Here's my
code, blatantly cribbed from Dinesh Asanka on sql-server-
performance.com.

And to clarify, this is the code I originally put in production- I
haven't tweaked it so that it'll work.