Post reply

Strange CHAR Behaviour With Linked Server

  • November 12, 2019 at 2:32 pm

    #3697057

    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.ZIPEND AS Address

    When I run this on the reporting server against the local reporting databases, I get the expected results.

    Capture

    When I run it against the linked server using four-part naming, I get.

    Capture1

    When I ran the following code I got an interesting result.

    LEN(l.AddressLine1+ ', ') AS Concatenated
    ,LEN(l.AddressLine1)AS NotConcatenated

    Capture2The 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

  • November 13, 2019 at 3:10 pm

    #3697378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • November 13, 2019 at 3:34 pm

    #3697399

    Neil

    I'm guessing ANSI_PADDING is OFF in SSMS but ON in whatever driver your linked server object uses to connect to the remote server.

    John

Viewing 3 posts - 1 through 2 (of 2 total)

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