Replication & DDL Triggers - any way to do it?

Discussion in 'microsoft.public.sqlserver.replication' started by M Bourgon, Jan 22, 2010.

  1. M Bourgon

    M Bourgon Guest

    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)
     
    M Bourgon, Jan 22, 2010
    #1
    1. Advertisements

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

    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/tutorial...-pocofirst-development-and-poco-template.aspx
     
    Jelena Spragg, Jul 1, 2010
    #2
    1. Advertisements

  3. M Bourgon

    Ben Thul Guest

    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

     
    Ben Thul, Jul 1, 2010
    #3
  4. M Bourgon

    M Bourgon Guest

    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(SQL.90).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, Jul 1, 2010
    #4
  5. M Bourgon

    M Bourgon Guest

    And to clarify, this is the code I originally put in production- I
    haven't tweaked it so that it'll work.
     
    M Bourgon, Jul 1, 2010
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.