ANSI_PADDING OFF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

ANSI_PADDING OFF

Author

Topic

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-01-12 : 11:54:56

Months ago we configured transactional replication and established the initial snapshot without incident. The other day we decided that we needed to re-initialize the subscriber's snapshot but now the ANSI_PADDING is set to OFF for one of the tables which, of course, causes the INSERTs to fail. My question is what could have come along and changed how the table schema gets generated by the snapshot?

It is a simple table named Calendar. There are no computed columns, no indexed views, no filtered indexes, etc. It's vanilla!

I was able to resolve the immediate issue by finding the appropriate schema file (.sch) in the snapshot directory and explicitly changing the ANSI_PADDING to ON. I'll need to do that again whenever the snapshot gets re-initialized.

My research turned up a reference to database triggers, which we have. I DROP/CREATE the triggers with the ANSI_PADDING set properly but that didn't help. I DROP the triggers and that doesn't help.

Any and all ideas are certainly welcome!

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-12 : 12:11:53

Looks like a bug in SQL 2005

http://connect.microsoft.com/SQLServer/feedback/details/334194/ansi-padding-options-in-replication-snapshot

Though, we do a lot of replication and I've never seen that one.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-01-12 : 12:24:29

Thanks for the feedback. This is exactly the issue that I'm seeing.

I'm still crying but now I'm not crying alone.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-12 : 12:38:17

Out of curiosity, can you post the DDL for the table in question?

Also, what versions/editions are publisher, distributor and subscriber?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-12 : 12:44:20

Just thought of an obvious workaround...

In the article properties, change the "Action if name in use" option to TRUNCATE. The default is to drop/create.

Let us know if that solves it.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-01-12 : 13:28:35

"Ask and ye shall receive" (Some Guy, born 1AD - died 33AD)

Publisher/Distributor: SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Enterprise
Subscribers: SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Enterprise

Table DDL

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[calendar](
[auto_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[date] [smalldatetime] NOT NULL,
[year] [int] NOT NULL,
[quarter] [int] NOT NULL,
[month] [int] NOT NULL,
[day] [int] NOT NULL,
[day_of_week] [int] NOT NULL,
[day_of_year] [int] NOT NULL,
[week_of_year] [int] NOT NULL,
CONSTRAINT [PK_calendar] PRIMARY KEY NONCLUSTERED
(
[auto_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object: Index [date_idx] Script Date: 01/12/2011 10:18:56 ******/
CREATE UNIQUE CLUSTERED INDEX [date_idx] ON [dbo].[calendar]
(
[date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

I'll try the "Action if name is in use" change but that'll need to wait for off-hours.

Thanks, again.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-01-14 : 12:43:59

Minor Update: I removed the database trigger from the publisher and re-created the publication and the issue resolved.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-14 : 13:12:48

Great. Did you modify the article properties?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-01-18 : 11:21:37

In re: Did you modify the article properties?

Negative. While the Calendar table probably won't change, the other articles might and we want to preserve orthogonality. We have tried using replication to track schema changes but have not found that it can be relied upon.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources