Server settings (Auto RTRIM Happening?)

Question

Server settings (Auto RTRIM Happening?)

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
February 12, 2009 at 6:27 am

#128138

Hi all,
OK I've been using this code for sometime in different places for concatenation columns together.
DECLARE @tbl TABLE
(id INT IDENTITY(1,1),
Col VARCHAR(10))
INSERT INTO @tbl
SELECT 'a' UNION ALL
SELECT 'a '
;WITH MyCTE (MyField)
as (SELECT Col + ', ' FROM @tbl FOR XML PATH(''))
SELECT LEN(MyField) as [len],MyField FROM MyCTE
today I found two strange things happen.
If I run the above code on our one server I get results
Len |Myfield|
----------- |
7 |a, a , |
On another server I get
Len |Myfield|
-----------|
6 |a, a ,|
what server setting would cause auto RTRIM?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I believe you want to check the ANSI_PADDING setting. That does affect how trailing blanks (spaces) are handled.

Jack Corbett
Consultant - Straight Path Solutions
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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

HI Jack.

OK I've check the:

"Default Connection options" for both servers.

The only difference is that the live server has "concat null yeilds null"

When I check the options under the properties for the db's on the different servers there are no differences.

Ansi Padding is off at both levels on both servers.

Thanks

Chris

P.S live server has auto RTRIM

SERVER VERSIONS

(Live)

Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86) Feb 26 2008 18:15:01

Windows NT 5.2 (Build 3790: Service Pack 2)

(Test)

Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86) Mar 3 2007 18:40:02

Windows NT 5.2 (Build 3790: Service Pack 2)

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Sorry to bring this up, but has anyone else got some advice on this ?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi Christopher

Sounds that some of your servers will not trim the spaces. I thought this would be a build-in functionality in SQL Server. I just googled a bit. After some equal threads I was referred back to this page 🙂 and the following article:

http://www.sqlservercentral.com/articles/T-SQL/63953/

If you only need the length you can use the DATALENGTH:

SELECT LEN(MyField) as [len],MyField, DATALENGTH(MyField) FROM MyCTE

This will return 14 (because the XML is NVARCHAR). Maybe somebody else has a solution for you...

Good luck!

Flo