SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum


ANSI PADDING, Trailing Whitespace, and Variable Length Character Colum

Author
Message
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19376 Visits: 14900
Thanks.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Wayne West
Wayne West
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2788 Visits: 3702
This has always been one of my biggest frustrations in SQL Server. I don't like having to trim my fields when I'm concatenating other fields, but I accept it. I just wish SQL Server had a Trim() function like Access that is essentially Ltrim(Rtrim([x])). I've considered writing a UDF for it and throwing it in Master & Model, just never bothered.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19376 Visits: 14900
Yes a true TRIM function would be nice. I actually had an issue recently when querying FoxPro data in SSIS around trimming. FoxPro has a 4 functions for trimming: LTRIM, RTRIM, TRIM, ALLTRIM. I assumed the TRIM function did RTRIM(LTRIM()), but it doesn't it only does RTRIM. So I was not matching on some data in a lookup because of LEADING spaces the I thought TRIM had removed!



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
TechnoPeasant
TechnoPeasant
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 71
Jack Corbett (9/12/2008)
Bill,

I'm not sure what you are exactly asking. Are you mentioning that if you just run the Selects you have listed in SSMS you get the same results?

When I run:

Select 'A' + 'A', 'A ' + 'A'



I get:


no_space one_space
-------- ---------
AA A A



I would guess that is because the QP is treating the literals as CHAR not VARCHAR.

Also SSMS connects to the server with ANSI_PADDING ON in the settings by default, you have to explicitly set it to off.


I was just trying to recreate your results but no matter what I do in SQL80 QA when run against a SQL90 DB instance I alway get the same padded result. Manipulating ANSI_PADDING at the session level appears to be ignored.

Select 'A' + 'A', 'A ' + 'A'



no_space one_space IS ALWAYSTHE RESULT
-------- ---------
AA A A



stephen.hendricks
stephen.hendricks
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 67
Useful information; thank-you.

What I am trying to determine, after running into various ANSI_PADDING errors in replication, is which tables currently have the ANSI_PADDING set on. I'd like to be able to predict how each table will behave and take proactive steps to insure that they will.

============================================================
I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19376 Visits: 14900
At least in SQL Server 2008 (I assume 2005 as well) you can query sys.tables and check the uses_ansi_nulls column 1 is ON, 0 is Off. I don't have any 2000 servers available to me at this time so I don't know how you'd find it there, but I assume it is possible.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
stephen.hendricks
stephen.hendricks
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 67
Thanks for the reply.

I assumed that the sys.tables.uses_ansi_nulls represented the SET ANSI_NULLS setting. BOL seems to indicate that it is. I just can't find any place where the ANSI_PADDING information is presented to the user.

The odd thing is that replication is complaining about this setting not being correct on my table but the table does not contain ANY character data.; only INT, SMALLINT, BIT, MONEY and SMALLDATETIME.

============================================================
I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19376 Visits: 14900
What do you mean by user? The ANSI_PADDING setting is one of the connection string settings and with SSMS and ADO.NET it defaults to ON. In SSMS you can make a global change through Tools->Options->Query Execution->SQL Server->ANSI or by individual query session under Query->Query Options->Execution-ANSI. OR you can just do SET ANSI_PADDING ON/OFF statement.

Using ADO.NET you need to execute a SET ANSI_PADDING OFF as part of the application.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
stephen.hendricks
stephen.hendricks
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 67
Allow me to clarify what I am looking for.

Setting or resetting the ANSI_PADDING value of the connection is simple enough, as you have pointed out.

For replication, it is necessary for the tables being replicated to conform to the same collection of SET options as for Indexed Views. As I'm sure you already know, these are:

ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
ARITHABORT ON
CONCAT_NULL_YIELDS_NULL ON
QUOTED_IDENTIFIER ON
NUMERIC_ROUNDABORT OFF

These settings are associated with the table based on their values at the time the table was created.

Since our existing tables don't always conform to these settings, we have to take the steps, as part of the Snapshot, to manually modify the .sch files to correct and variances before applying the Snapshot to the subscriber.

Conceptually, what I'd like to know is, "Which existing tables don't have these settings correctly configured?"

sys.tables.uses_ansi_nulls does show me the settings for ANSI_NULLS. (So far, so good)

ObjectProperty() does show me the settings for ANSI_NULLS and QUOTED IDENTIFIER. (Hey, even better! But not all the way there.)

What I want is visibility into the other five settings (ANSI PADDING being my immediate concern) so that I, the user, can determine if I'm going to run into trouble when I replicate the table. SQL Server clearly has this information somewhere since it complains if the setting is wrong. Getting to this information is my quest.

The long term goal is to determine if it is worth while to re-configure any particular table to have the proper settings. Knowing the current configuration would be the first step in this process.

============================================================
I believe I found the missing link between animal and civilized man. It is us. -Konrad Lorenz, Nobel laureate (1903-1989)
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19376 Visits: 14900
Hey, I ran a Profile trace and then scripted out a table definition as that gets you the settings. The setting for ANSI_PADDING is in sys.all_columns is_ansi_padded.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search