Post reply

odd behaviour between SQL instances

  • January 22, 2009 at 12:28 pm

    #200326

    HI, I have an odd problem I have not seen before.

    I have two instances of SQL 2005 sp3 running on the same machine, a test and a dev environment. The databases are almost identical (some schema differences) but I have a proc that exists in both places which uses a select mocked out below:

    SELECT DISTINCT

    CSL.id,

    RTRIM(CSL.name) AS name,

    RTRIM(CSL.tag) AS tag

    FROM

    TABLE1 AS CSL

    ORDER BY

    CSL.name

    Now this is just a select statement in the proc, but when I run it on one instance the sql complains because of the CSL.name with the error:

    Msg 145, Level 15, State 1, Procedure USP_NAME, Line 63

    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    So I have to remove the CSL. from the proc before it will parse.

    yet exactly the same query on the other instance runs ok with no errors??? why would the behaviour between two instances on the same version of sql differ?

    Thanks

  • January 22, 2009 at 12:32 pm

    #931391

    Check the compatibility of the databases you are running that in, on both instances. One of them is probably set on 90 and the other on 80. That's where I've seen that problem before.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 22, 2009 at 12:40 pm

    #931394

    Thanks G, nail hit firmly on the head there.

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

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