Home Dashboard Directory Help

"Generate scripts..." wizard adds dozens of "SET ANSI_PADDING OFF" by PavlinII


Status: 

Closed
 as By Design Help for as By Design


11
0
Sign in
to vote
Type: Bug
ID: 726651
Opened: 2/26/2012 5:03:38 AM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Regarding to documentation, ANSI_PADDING OFF will be allways set to ON in future versions (future since 2008) and setting it off will cause error.

BUT "Generate and Publish script" still adds dozens of SET ANSI_PADDING OFF statements between CREATE TABLE statement and it's constraints creation.
Details
Sign in to post a comment.
Posted by Michael K Campbell on 2/17/2015 at 10:53 AM
This REALLY is a bug. If BOL states that use of SET ANSI_PADDING OFF should be avoided, but SSMS _ARBITRARILY_ throws it out into the scripted definitions of objects (where that setting is NOT set to OFF), then this is a bug.

More:
http://sqlmag.com/blog/sql-server-management-studio-s-ugly-ansipadding-bug
Posted by Microsoft on 5/6/2013 at 10:39 AM
In both cases the issue is related to deprication work that we didn't address yet.
In BOL (http://msdn.microsoft.com/en-us/library/ms187403) we have mentioned the following: "In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature." Currently is_ansi_padding_on is 0 by default in sys.databases. Same behavior exists for is_ansi_nulls_on; it’s 0 by default.

The issue will be resolved after above deprication work takes a place. Currently I will resolve this issue as by design.

Thank you
Alex Grach
Posted by Erland Sommarskog on 7/13/2012 at 3:08 PM
I reported this already during the SQL 2005 beta, somehow I grew tired of nagging about it. (There were more bad SET commands generated in those days). But it's simple: you should *never* generate any SET OFF commands *unless* the object in question was create with this command OFF. And in this case, you should add an ON command at the end.
Posted by Microsoft on 7/3/2012 at 2:36 PM
Thank you for reporting this issue, I'm sorry for the delay somehow this issue was not on our radar. We are investigating and we will get back to you shortly.

Thanks,

Alex Grach [MSFT SQL SERVER]
Sign in to post a workaround.
Posted by Michael K Campbell on 8/5/2014 at 3:10 PM
The work-around posted here is pretty simple to implement:
http://sqlmag.com/blog/sql-server-management-studio-s-ugly-ansipadding-bug

However the bug should be addressed sooner rather than later - it's an ugly bug/problem and can cause some pretty ugly issues given how setting ANSI_PADDING OFF 'stays' or 'sticks' with the session until explicitly removed.