Results 1 to 13 of 13

Thread: Concatenate records problem with order by

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,794

    Concatenate records problem with order by

    This is a weird problem that is happening only on some SQL servers.

    The following code
    Code:
    declare @ServerApps varchar(max)
    declare @apps table(AppName varchar(100))
    
    insert @apps(AppName) values('Application A')
    insert @apps(AppName) values('Application B')
    insert @apps(AppName) values('Application C')
    
    select @ServerApps = ISNULL(@ServerApps, '') + '/' + AppName from @apps
    --order by AppName
    
    select @ServerApps
    returns: "/Application A/Application B/Application C"

    BUT, if I uncomment "order by AppName", then on some servers I get the same result, but other server I only get "/Application C"

    First I thought it's the server version, but then I tried on 2 different SQL Server 2012, and on one I got the full strings concatenated, and the other one I only got "/Application C"

    It only happens when there is an ORDER BY

    Does anyone know what could be wrong? any settings in SQL Server that makes it behave like this?

    Can you guys try it on your SQL Servers? what do you get?

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,087

    Re: Concatenate records problem with order by

    I just tried it on 4 different SQL boxes - all worked fine for me both with and without the ORDER BY

    Show the execution plan for both the working and non-working run on system. Do they look any different?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,794

    Re: Concatenate records problem with order by

    The difference between the execution plan is the order the sort is done:

    On the server that returns the correct result (all records concatenated):

    Name:  sql_right_return.jpg
Views: 107
Size:  10.0 KB


    And on the server that returns only one record (the last record):

    Name:  sql_wrong_return.jpg
Views: 110
Size:  9.6 KB

  4. #4
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Concatenate records problem with order by

    what are the results and execution plans from both servers when you comment out the 'ORDER BY' part?
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  5. #5

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,794

    Re: Concatenate records problem with order by

    The result without ORDER BY is on both servers return all records concatenated, and execution plan is:

    Name:  sql_right_return.jpg
Views: 107
Size:  10.0 KB

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,794

    Re: Concatenate records problem with order by

    It's been a week since I posted my question. No one has any clue on what's going on?

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,493

    Re: Concatenate records problem with order by

    Honestly, I'm surprised it works period. When ever I've tried to order by an alias like that, I get errors.
    The only thing that comes to mind it to check the collation on servers, and databases, and maybe also check the Compatibility Options. Easily any of those could cause differences in the results.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,087

    Re: Concatenate records problem with order by

    Change it from this:

    select @ServerApps = ISNULL(@ServerApps, '') + '/' + AppName from @apps

    to this:

    select @ServerApps = ISNULL(@ServerApps, '') + '/' + IsNull(AppName,'Found a NULL in the APPNAME') from @apps

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,794

    Re: Concatenate records problem with order by

    Quote Originally Posted by techgnome View Post
    Honestly, I'm surprised it works period. When ever I've tried to order by an alias like that, I get errors.
    The only thing that comes to mind it to check the collation on servers, and databases, and maybe also check the Compatibility Options. Easily any of those could cause differences in the results.

    -tg
    Collation and Compatibility Options seem to be the same on both servers.

    Quote Originally Posted by szlamany View Post
    Change it ...to this:
    select @ServerApps = ISNULL(@ServerApps, '') + '/' + IsNull(AppName,'Found a NULL in the APPNAME') from @apps
    Didn't make any difference since there are no NULLs

    But, just for the fun of it, I tried to put a TOP 100, and now on the server that before it was returning incorrect (only one row), now it returns ALL rows concatenated!

    The execution plan is different, even though both servers return the same thing (when using top 100 with ORDER BY)

    The query:
    Code:
    declare @ServerApps varchar(max)
    declare @apps table(AppName varchar(100))
    
    insert @apps(AppName) SELECT 'Application A' UNION ALL
    SELECT 'Application B' UNION ALL
    SELECT 'Application C'
    
    select TOP 100 @ServerApps = ISNULL(@ServerApps, '') + '/' + AppName from @apps
    order by AppName
    
    select @ServerApps
    This is the server that returns incorrect (one row), when not using TOP 100 with order by:

    Name:  c.jpg
Views: 89
Size:  8.0 KB

    and this is the server that returns all rows regardless if I have ORDER BY:

    Name:  d.png
Views: 85
Size:  8.0 KB
    Last edited by CVMichael; Dec 8th, 2017 at 01:10 PM.

  10. #10

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,794

    Re: Concatenate records problem with order by


  11. #11
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: Concatenate records problem with order by

    Great find CVMichael....

    i personally never came accross this method of concatenation and was intrigued at how it works, it seems though that its got as many problems as it solves .
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,087

    Re: Concatenate records problem with order by

    Quote Originally Posted by CVMichael View Post
    Here's what I found:
    Wow - solid google skills dude!

    @gbeats - I've used simple concatenation with a STRING variable in a SELECT statement on many, many occasions. Never had issue...
    Last edited by szlamany; Dec 13th, 2017 at 07:54 PM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Concatenate records problem with order by

    From a limited configuration experience with MS Sql, perhaps the one server has slightly different settings.
    Perhaps ask szlamany whether there is a way to copy the configurations from a working server and update the one that is not working properly - without needing to re-install etc
    If there is a way to do this you could copy the two configuration files and compare them in say notepad++ to see where the differences are...?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width