SET ANSI_PADDING ON

Question

SET ANSI_PADDING ON

JWOL

Hall of Fame

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

#132522

When is the database setting "ANSI_PADDING" used? Books Online says that this is on by default but in a fresh install of SQL Server I find that this database option is always off. My session settings (ansi padding on) always overrides the database setting anyway.

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

JWOL (11/26/2008)
When is the database setting "ANSI_PADDING" used? Books Online says that this is on by default but in a fresh install of SQL Server I find that this database option is always off. My session settings (ansi padding on) always overrides the database setting anyway.

Where in BOL did you read that ANSI_Padding is ON by default. In 2005 that's not the case, it only says that in future versions it will be set ON by default. I think in 2008 that is the case, but I don't have a 2008 machine available right now.

[font="Verdana"]Markus Bohse[/font]

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

It's actually the connection-level setting which is on by default - my mistake. My problem is that even after both of these settings have been set to on, when I create a snapshot for replication, the .sch file for any tables only containing non-character columns has SET ANSI_PADDINGS OFF. Is there somewhere else where this can be set in SQL Server?

mdavide SSC Veteran Points: 254 More actions · Answer 3

Did this ever get resolved. I'm experiencing the exact same thing.

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

I've searched all 10 cumulative updates for SP3 and there is no mention of a fix for this issue with ANSI_PADDING OFF in the replication *.sch files. This is definitely an issue in SQL Server 2005 which requires manual attention for what is supposed to be an automated process (replication).

Is there any kind of workaround other than manually altering the *.sch files in the Publisher directory with SET ANSI_PADDING ON ? Thank-you for any information you can give.

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

I think I found that my problem was a database trigger that was active on the subscriber. I disabled that and the ANSI PADDING setting was correct thereafter so maybe it's worth checking that.

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

I can understand that the underlying problem was caused by some dynamic SQL in the DDL trigger but it can't be seen as a solution to this issue disabling and re-enabling the DDL trigger everytime a Subscription needs to synchronise.

Thanks for the info though! 🙂

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

There was no dynamic SQL in the trigger and I didn't re-enable it so it wasn't a problem for me. Do you have any database triggers on your subscriber?

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

I've read elsewhere that a DDL trigger on the source database can cause problems with this but

a.) change-control forbids me to disable a trigger that is logging DB changes

b.) must this be done manually every time a database is scheduled to replicate?

I can't find any way round this....:w00t: