ANSI_PADDING

Question

Post reply

ANSI_PADDING

JWOL

Hall of Fame

Points: 3471
More actions
November 26, 2008 at 5:47 am

#203498

When initialising a transactional snapshot I get the following error on any table that has only non-character fields:
CONDITIONAL failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
Looking at the .sch files, they all have SET ANSI_PADDING OFF. I've tried changing the database setting to be on by default but it makes no difference. If I manually edit a file then the object gets replicated. I have hundreds of files though so I can't go editing them all after each snapshot.
Does anyone know of a way around this?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

vefa Mr or Mrs. 500 Points: 589 More actions · Answer 1

If subscriber db has database trigger, you get this type of error.

Remove database trigger and run initialisation and then create database trigger gain.

JWOL Hall of Fame Points: 3471 More actions · Answer 2

Thanks for the suggestion. I've tried disabling all triggers but still get the same error.

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 3

Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Are you using one of these on the replicated tables at the subscriber?

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

JWOL Hall of Fame Points: 3471 More actions · Answer 4

No, but looking at the .sch files which have ANSI PADDING set to off, they are all for tables that have no character columns. If a table has a character column then the file has the correct ANSI PADDING setting.

dbanuj Valued Member Points: 61 More actions · Answer 5

Thanks..It was really help me, as i was facing same issue and it was got resolved after deleting the db trigger.

SQL Swerver Hall of Fame Points: 3074 More actions · Answer 6

I had this same error when trying to initialise a Subscription. I checked the <subname>.sch file on the Publisher and noticed SET ANSI_PADDING OFF.

I amended the file to SET ANSI_PADDING ON and the initialisation succeeded.

WHY does SQL Server set this to OFF and how can I resolve this in future Subscription synchonisations as it is required to be ON.

Also, isn't this functionality being deprecated?

David Kirchner SSC Enthusiast Points: 115 More actions · Answer 7

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.