Replication & DDL Triggers - any way to do it?
in [Replication]
|
From: Jelena on 1 Jul 2010 05:01 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
From: Ben Thul on 1 Jul 2010 08:18 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 On Jul 1, 4:01 am, Jelena Spragg wrote: > 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 Templatehttp://www.eggheadcafe.com/tutorials/aspnet/488279c4-214b-47ef-b152-9...
From: M Bourgon on 1 Jul 2010 17:54 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
From: M Bourgon on 1 Jul 2010 17:55 On Jul 1, 4:54 pm, M Bourgon <bour...(a)gmail.com> wrote: > 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.
|
Pages: 1 Prev: SQL merge setup question Next: Is it possible to replicate Store Procedure ? |