moving from sql 2000 to sql 2005 manualy

  • The query uses non-ANSI outer join operators ("*=" or "=*").

    To run this query without modification, please set the compatibility level for current database to 80 or lower,

    using stored procedure sp_dbcmptlevel.

    It is strongly recommended to rewrite the query using

    ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN).

    In the future versions of SQL Server, non-ANSI join operators will not be supported even

    in backward-compatibility modes.

  • I'm not sure why this was set up as a poll. If what you're trying to do is get advice on how to fix this error, I'd rewrite the joins to be ANSI-92 standard (use outer joins). Books Online has data on exactly how to do this, but here's a sample:

    select *

    from dbo.Table1,

    dbo.Table2

    where Table1.Col1 *= Table2.Col1

    changes to:

    select *

    from dbo.Table1

    left outer join dbo.Table2

    on Table1.Col1 = Table2.Col1

    The first one is "the old way", the second one is the ANSI-92 ("new") way.

    - 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

  • this query Executing fine and displayed currect output in SQL2000

    SELECT *

    FROM tbl_dxu_entitydetails WITH (NOLOCK), tbl_dxu_templatedetails WITH (NOLOCK), tbl_DXU_LookUp WITH (NOLOCK)

    WHERE tbl_dxu_entitydetails.FieldID *= tbl_dxu_templatedetails.ActualFieldId

    AND tbl_dxu_entitydetails.FieldID *= tbl_DXU_LookUp.DestinationFieldID

    AND tbl_dxu_entitydetails.EntityID = (SELECT EntityId

    FROM tbl_DXU_TemplateMaster WITH (NOLOCK)

    WHERE tbl_DXU_TemplateMaster.TemplateID = 24)

    I have converted this query for SQL2005 Like this

    SELECT *

    FROM tbl_dxu_entitydetails WITH (NOLOCK)

    left OUTER JOIN tbl_dxu_templatedetails WITH (NOLOCK)

    ON tbl_dxu_entitydetails.FieldID = tbl_dxu_templatedetails.ActualFieldId

    left OUTER JOIN tbl_DXU_LookUp WITH (NOLOCK)

    on tbl_dxu_entitydetails.FieldID = tbl_DXU_LookUp.DestinationFieldID

    WHERE

    tbl_dxu_entitydetails.EntityID = (SELECT EntityId

    FROM tbl_DXU_TemplateMaster WITH (NOLOCK)

    WHERE tbl_DXU_TemplateMaster.TemplateID = 24)

    Both the query displayed the currect output in SQL2000 and SQL 2005 but when i put filter condition like this

    ******************************************

    AND tbl_dxu_templatedetails.TemplateID = 24

    AND tbl_DXU_LookUp.TemplateID = 24

    AND Show = 1

    ORDER BY tbl_dxu_entitydetails.IsMandatory DESC, FieldID ASC

    **************************************************

    Then its displayed wrong output..........

    So please help me..................

  • These two:

    AND tbl_dxu_templatedetails.TemplateID = 24

    AND tbl_DXU_LookUp.TemplateID = 24

    Need to be in the Join criteria, not in Where clause. I'm not sure about the Show=1 part, since I don't know which table that's in. If it's in tbl_dxu_entitydetails, it's fine. If it's in one of the tables you are left joining to, it needs to be in the Join clause (after "On").

    Something like this is what you'll end up with:

    SELECT *

    FROM tbl_dxu_entitydetails WITH (NOLOCK)

    LEFT OUTER JOIN tbl_dxu_templatedetails WITH (NOLOCK)

    ON tbl_dxu_entitydetails.FieldID = tbl_dxu_templatedetails.ActualFieldId

    AND tbl_dxu_templatedetails.TemplateID = 24

    LEFT OUTER JOIN tbl_DXU_LookUp WITH (NOLOCK)

    ON tbl_dxu_entitydetails.FieldID = tbl_DXU_LookUp.DestinationFieldID

    AND tbl_DXU_LookUp.TemplateID = 24

    AND tbl_dxu_entitydetails.FieldID is not null

    WHERE

    tbl_dxu_entitydetails.EntityID =

    (SELECT EntityId

    FROM tbl_DXU_TemplateMaster WITH (NOLOCK)

    WHERE tbl_DXU_TemplateMaster.TemplateID = 24)

    AND Show = 1

    ORDER BY tbl_dxu_entitydetails.IsMandatory DESC, FieldID ASC

    Just make sure that "Show = 1" is in the right part, depending on which table it's in.

    The reason for this is that, in a Left Outer Join, any criteria on the left table need to be in the Join clause, or they make it into an Inner Join.

    - 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

  • GSquared (5/21/2008)


    The reason for this is that, in a Left Outer Join, any criteria on the left table need to be in the Join clause, or they make it into an Inner Join.

    methinks you meant to say

    ....any criteria on the right table...

    Of course - that statement holds true unless your criteria allows for the fact that the RIGHT table columns might be null (in which case the optimizer honors the LEFT OUTER JOIN).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt Miller (5/21/2008)


    GSquared (5/21/2008)


    The reason for this is that, in a Left Outer Join, any criteria on the left table need to be in the Join clause, or they make it into an Inner Join.

    methinks you meant to say

    ....any criteria on the right table...

    Of course - that statement holds true unless your criteria allows for the fact that the RIGHT table columns might be null (in which case the optimizer honors the LEFT OUTER JOIN).

    You're correct. I meant the right table.

    - 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

Viewing 6 posts - 1 through 5 (of 5 total)

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