SQL Server Performance

Query failing on one server and does not on another

Discussion in 'ALL SQL SERVER QUESTIONS' started by Ramkumar Mu, Apr 16, 2014.

  1. Ramkumar Mu New Member

    Hi All,
    Let me know if anyone have faced or solved this error before in SQL Server 2000...
    I have a query similar to this inside a SP...

    SELECT *
    FROM Table1 A
    INNER JOIN Table2 B
    ON A.Col1 = B.Col1
    INNER JOIN Table3 C
    ON B.Col1 = C.Col1
    INNER JOIN Table4 D
    ON A.Col1 = D.Col1

    When the first inner join (joining Table2) in the query is commented out, the query should have throw an error since the second inner join is dependant on Table2. But the SP is created successfully without any errors.
    i.e. SELECT *
    FROM Table1 A
    --INNER JOIN Table2 B
    -- ON A.Col1 = B.Col1
    INNER JOIN Table3 C
    ON B.Col1 = C.Col1
    INNER JOIN Table4 D
    ON A.Col1 = D.Col1
    We did the same on a different server (say ServerB). This time the query threw an error (this is the expected behavior) -
    "Msg 107, Level 16, State 2, Procedure TestAlias, Line 5
    The column prefix 'B' does not match with a table name or alias name used in the query."
    Any thoughts why the query did not fail on Server1 and failed on Server2?
    Is there any configuration/debugging setting that prevents from failing on Server1?
  2. srinunaik251 New Member

    Hello Ramkumar,
    Hope this will helps you.

    definitely you'll get the error bcoz see the second query you commented first join(--INNER JOIN Table2 B
    -- ON A.Col1 = B.Col1) so this means your not using table B in the query still your using on that tableB on the second join(INNER JOIN Table3 C ON B.Col1 = C.Col1).
    If you want to resolve just uncomment the that join or you can join Table C with any other table except the Table B.

    Thanks
  3. AJITH123 Member

    try after clearing the cache...something like this

    DBCC DROPCLEANBUFFERS
    DBCC FREESYSTEMCACHE('SQL Plans')

Share This Page