asked on

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

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JestersGrind

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

Greg

aspnetdev

ASKER

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

JestersGrind

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