SQL Server 2000 and 2005 Compatibility

Hi,
I have a SQL server 2000 database running on SQL 2005 engine with compatibility mode set to 2000.Now when i generate my database script and run it on the 2005 database i get following error :

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 tried to check on the issue and one of the Store proc had following

select @OverRiddenValue = OverriddenAmount from
                        TaxPosition tp, PreAdoptionData Pd
                        where tp.id *= pd.PositionId
                        and pd.PeriodYear = @PreAdoptionYear
                        and pd.PeriodQtr = @PreAdoptionQtr
                        and tp.id = @PositionId

Can any one tell me how to fix this.
Thanks
aspnetdevAsked:
Who is Participating?
 
JestersGrindCommented:
You can change the above query to use a LEFT OUTER JOIN.  Just to clarify:

*= is a LEFT OUTER JOIN
=* is a RIGHT OUTER JOIN

You code should look like the code below.

Greg



SELECT @OverRiddenValue = OverriddenAmount 
FROM TaxPosition tp LEFT OUTER JOIN  
	   PreAdoptionData Pd ON tp.identity_columns AS ic = pd.PositionId
WHERE pd.PeriodYear = @PreAdoptionYear AND pd.PeriodQtr = @PreAdoptionQtr AND tp.id = @PositionId

Open in new window

0
 
JestersGrindCommented:
By the way if you are getting that error message, the database is not running in SQL 2000 compatibility mode(compatibility level 80)

Greg


0
 
aspnetdevAuthor Commented:
Thanks for the reply Greg but my question is why are you using this :
tp.identity_columns AS ic could i not write simply as

SELECT @OverRiddenValue = OverriddenAmount FROM
           TaxPosition tp LEFT OUTER JOIN  
           PreAdoptionData Pd ON tp.id = pd.PositionId
WHERE pd.PeriodYear = @PreAdoptionYear AND pd.PeriodQtr = @PreAdoptionQtr AND tp.id = @PositionId

Does it make sense?
Thanks
0
 
JestersGrindCommented:
Sorry about that.  I'm using SQL Prompt, which is an SQL intellisense-type tool.  Sometimes if I'm not careful, it resolves things incorrectly (especially when the query I'm writing is against a table that doesn't exist on my end) and puts wrong information in the query.  

Anyway, the query that you have above is correct.  Sorry for the confusion.

Greg


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.