DDL Trigger Implementation Problems

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  July 8, 2009 at 9:02 am

  #133675

  Hi All

  I have implemented DDL trigger on my database and store all the values on a table (the code is as shown below), when i run an alter procedure it throws me and error stating that space not enough on XML, i ahve currently disabled the trigger, how can i overcome this.

  CREATE TABLE [dbo].[DBLog](

  [ID] [int] IDENTITY(1,1) NOT NULL,

  [EventTime] [datetime] NULL,

  [EventType] [varchar](15) NULL,

  [ServerName] [varchar](25) NULL,

  [LoginName] [varchar](100) NULL,

  [DatabaseName] [varchar](50) NULL,

  [SchemaName] [varchar](25) NULL,

  [ObjectType] [varchar](25) NULL,

  [ObjectName] [varchar](25) NULL,

  [CommandText] [varchar](max) NULL,

  [SPID] [varchar](100) NULL

  ) ON [PRIMARY]

  -- Trigger Creation

  CreateTRIGGER DDLTrigger ON Database -- Create Database DDL Trigger

  FOR DDL_DATABASE_LEVEL_EVENTS -- Trigger will raise when creating a Table

  AS

  SET NOCOUNT ON

  DECLARE @xmlEventData XML

  -- Capture the event data that is created

  SET @xmlEventData = eventdata()

  INSERT INTO DBLOG

  (EventTime,EventType,ServerName,LoginName,DatabaseName,SchemaName,ObjectType,ObjectName,CommandText,SPID)

  SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

  CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/SchemaName)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),

  CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),

  CONVERT(VARCHAR(MAX), left(@xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')),3999),

  CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/SPID)'))

  GO

  Thanks in advance for your help

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 8, 2009 at 9:22 am

  #1021784

  What's the exact error that you get?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  July 9, 2009 at 2:14 am

  #1022211

  Hi Gail

  Conversion of one or more characters from XML to target collation impossible [SQLSTATE 42000] (Error 6355). The step failed.

  i have disabled all the triggers that i have made,

  One more doubt , will this triggers have a load on live systems, if so how to measure that load

  Thanks

  🙂

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  July 9, 2009 at 2:14 am

  #1022213

  Hi Gail

  Conversion of one or more characters from XML to target collation impossible [SQLSTATE 42000] (Error 6355). The step failed.

  i have disabled all the triggers that i have made,

  One more doubt , will this triggers have a load on live systems, if so how to measure that load

  Thanks

  🙂

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  July 9, 2009 at 2:15 am

  #1022214

  Hi Gail

  Conversion of one or more characters from XML to target collation impossible [SQLSTATE 42000] (Error 6355). The step failed.

  i have disabled all the triggers that i have made,

  One more doubt , will this triggers have a load on live systems, if so how to measure that load

  Thanks

  🙂

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  July 9, 2009 at 2:45 am

  #1022217

  ops problem with posting, i have mailed to webmaster,