I'm in the process of creating a suite of reports to use when our replicated reporting server is unavailable. The new reports are to use the databases on the live servers to maintain some business continuity while work is being done on the reporting boxes. I'm converting the existing stored procs to use four-part naming from the reporting server to connect to the live databases. Unless there was a glaring error I wasn't intending to change the logic or the returned columns. I have discovered a very odd situation though and I wonder if anybody can shed any light on it.
One of the stored procs builds an address string using the code below. The AddressLineN columns are all CHAR(50) and ZIP is CHAR(10)
CASE WHEN LEN(l.AddressLine1) = 0 THEN '' ELSE l.AddressLine1 + ', ' END +
CASE WHEN LEN(l.AddressLine2) = 0 THEN '' ELSE l.AddressLine2 + ', ' END +
CASE WHEN LEN(l.AddressLine3) = 0 THEN '' ELSE l.AddressLine3 + ', ' END +
CASE WHEN LEN(l.ZIP) = 0 THEN '' ELSE l.ZIP END AS Address
When I run this on the reporting server against the local reporting databases, I get the expected results.
When I run it against the linked server using four-part naming, I get.
When I ran the following code I got an interesting result.
LEN(l.AddressLine1+ ', ') AS Concatenated
,LEN(l.AddressLine1) AS NotConcatenated
The sharp-eyed amongst you may notice that the length of 93 Manchester Road is actually 18 characters, that's because I've had to fudge my examples to stay on the right side of GDPR. The screen shot of the result is the actual value though.
It appears that the trailing spaces aren't being stripped out away when the column from the linked server is concatenated with the ','.
Has anybody seen this before?
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537