SQL Server Performance

Can anyone tell me why this procedure not works in sql2005 but works fine in Development server sql2000?

Discussion in 'Getting Started' started by shail87, Jan 5, 2009.

  1. shail87 New Member


    Can anyone tell me why this procedure not works in sql2005 but works fine in Development server sql2000?
    if i commented the bottom is null than only it works fine in both Production and development database servers
    select r.Description, b.build, s.serialno, bc.childserialno
    From TbltcvRev r,
    Tblsn s,
    Tblsnbuild b,
    TblsnbuildContent bc,
    TblTCVariant tcv,
    TblTypecode tc
    where r.tcvrevid=b.tcvrevid and
    r.tcvid=tcv.tcvid and
    tc.tcid=tcv.tcid and
    b.serialno *= bc.childserialno and
    b.build *= bc.childbuild and
    s.serialno='FC6084907811' and
    b.serialno='FC6084907811' and
    b.build = 1 and
    (
    Select ObsoleteDate
    From TblSNBuild
    Where SerialNo = bc.serialno and
    Build = bc.build
    ) is null
  2. FrankKalis Moderator

    The error message you receive should be fairly self-explaining. [;)]
    The old-style OUTER JOIN syntax
    b.serialno *= bc.childserialno and
    b.build *= bc.childbuild and
    is not supported in SQL Server 2005 any longer. You need to rewrite this statement to use the proper ANSI SQL OUTER JOIN syntax.
  3. shail87 New Member

    Could you pls suggest me how to rewrite the new statement to use the proper
    ANSI SQL OUTER JOIN syntax
    I am a fresher guy for sql 2005/2000
    thanks for your fast reply.
  4. FrankKalis Moderator

    This would be something like
    FROM
    ...
    Tblsnbuild b,
    LEFT JOIN
    TblsnbuildContent bc ON b.serialno = bc.childserialno AND b.build = bc.childbuild,

    Just look up the JOIN syntax in the SQL Server Books Online. There are plenty of examples.
  5. Adriaan New Member

    Frank, you forgot to drop the commas ...
  6. shail87 New Member

    Hi Frank,
    I ran the query which you have suggested and got the error message :--
    Msg 1016, Level 15, State 2, Line 38 Outer join operators cannot be specified in a query containing joined tables
    select r.Description, b.build, s.serialno, bc.childserialnoFrom
    TbltcvRev r,Tblsn s
    ,Tblsnbuild b
    ,TblsnbuildContent bc, TblTCVariant tcv
    ,TblTypecode tcwhere r.tcvrevid=b.tcvrevid and r
    .tcvid=tcv.tcvid andtc
    .tcid=tcv.tcid andb
    .serialno *= bc.childserialno andb.build *= bc.childbuild and s
    .serialno='FC6084907811' and b.serialno='FC6084907811' and b
    .build = 1 and --(Select ObsoleteDate From TblSNBuild
    --Where SerialNo = bc.serialno and Build = bc.build ) is null
    (select obsoletedate FROM ...Tblsnbuild bLEFT JOIN TblsnbuildContent bc
    ON b.serialno = bc.childserialno AND b.build = bc.childbuild)
  7. Adriaan New Member

    The *= and =* ways of writing joins is no longer supported in SQL 2005. Not only in subqueries, but in any type of query. So make the same changes to your main query.
  8. FrankKalis Moderator

    [quote user="Adriaan"]
    The *= and =* ways of writing joins is no longer supported in SQL 2005. Not only in subqueries, but in any type of query. So make the same changes to your main query.
    [/quote]
    True. Sorry if I have expressed myself unclear.
  9. FrankKalis Moderator

    [quote user="Adriaan"]
    Frank, you forgot to drop the commas ...
    [/quote]
    Oops, good catch! Thanks. [:)]

Share This Page