For a while I ignored the 'Drop Database Trigger' because I have run this trigger for years with no snapshot\replication trouble. I tried it and it is now (4/2015) what I need to fix the ANSI_Padding OFF transaction replication trouble I have on SQL 2014. I just wanted to add details on how I did this.
Since I rebuild the snapshot every weekend, I added scripts to be run with the snapshot. See the picture.
Publication Properties: Snapshot: Run additional scripts.
[highlight=#ffff11]The before snapshot script is:[/highlight] HPAlloy Replication Pre Snapshot script.sql
USE [HPAlloy]
GO
/****** Object: DdlTrigger [DDLTrigger_AuditDB] Script Date: 5/1/2015 11:55:53 PM ******/
DROP TRIGGER [DDLTrigger_AuditDB] ON DATABASE
GO
/****** Object: DdlTrigger [DDLTrigger_AuditDB] Script Date: 5/1/2015 11:55:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
go
[highlight=#ffff11]To reset my database trigger I run:[/highlight] HPAlloy Replication Post Snapshot script.sql
USE [HPAlloy]
GO
/****** Object: DdlTrigger [DDLTrigger_AuditDB] Script Date: 5/1/2015 11:55:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
go
CREATE TRIGGER [DDLTrigger_AuditDB]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, RENAME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData XML ;
SET @EventData = EVENTDATA();
DECLARE @ip VARCHAR(32) ;
SET @ip =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
INSERT AuditDB.dbo.DDLEvents
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO
ENABLE TRIGGER [DDLTrigger_AuditDB] ON DATABASE
GO
The trigger code is not an original thought of mine, I believe it is from a post from Aaron Bertrand.