Post reply

Same Query Different Servers Different Results

  • June 3, 2009 at 10:34 am

    #130927

    I have a query that returns different results depending on which server it is ran on even though the query points to the same server. For example, I have ServerA and ServerB. I run the following query on both servers. ServerA returns incorrect results while ServerB returns correct results. What are some things I should look for?

    select a.JobID, a.Code

    from ServerB.DB.dbo.Table1 as a

    where a.Amount > 0

    and a.Trust is null

    and a.Source <> 'City'

    and a.Code in (select b.Code from ServerB.DB.dbo.SubProducts as b)

    --and isnull(a.Code, '') in (select b.Code from ServerB.DB.dbo.SubProducts as b)

    --and a.Code in (select b.Code from ServerB.DB.dbo.SubProducts as b where isnull(a.Code, '') <> '')

    The line containing "and a.Code in" is the original that produces the incorrect results on ServerA but correct results on ServerB. If I use either of the commented out lines instead of the original, I get the correct results for both servers. I have already verified that no NULL values are in the Code column in either table. Any help would be appreciated.

  • June 3, 2009 at 10:46 am

    #1004173

    dumb question first...are you sure Table1 is identical on ServerA and ServerB? different data would of course produce different results.

    second, there is a sub select with this code:

    select b.Code from ServerB.DB.dbo.SubProducts as b

    should that be

    select b.Code from ServerB.DB.dbo.SubProducts as b WHERE b.Code IS NOT NULL

    you might get inconsistent results if there is a b.Code that is null in one server, but not the other.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 3, 2009 at 10:48 am

    #1004175

    There are not two different tables. The query points to ServerB whether it is ran on ServerA or ServerB. Also, there is no NULL values in the sub-select.

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

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