Msg 4147, Level 15, State 1

Question

Msg 4147, Level 15, State 1

rs

SSCrazy

Points: 2819
More actions
January 20, 2009 at 10:15 am

#125779

I am getting the following error even though I have setup to compatibility to 80.
Msg 4147, Level 15, State 1, Procedure usp_ValidatePriceIndex, Line 43
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.
Any help would be appriciated.

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

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

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 1

- Cross db queries ??

- we have been warned as from sql7.0 (if I'm correct sp2) that this kind of joins were obsolete. And with every migration project I've done until now, many of these queries needed to be revised, because some PITA DBA ( me 😛 )

didn't want so have SQL2005 host level 80 db in a production environment.

Normally altering the dblevel to 80 would do the trick, but as MS recommends, level 80 should not be allowed for a long time on a level 90 or 10 instance.

Also many system functions will not work on a level 80 db.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rs SSCrazy Points: 2819 More actions · Answer 2

Its not crosstab query, I am joining 4 tables. I have changed to 80 but still getting the same error.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Is the database you're running it from in compat mode 90? If, say, you're running it from master and using 3-part names to refer to the proc, then it will still fail because the db that you're in (master) is in mode 90.

If not, post the query, and the results from running sp_dbcmptlevel

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 4

hmm... indeed posting the query would indeed demistify a little bit.

Just copy/paste it as it is in your actual application/script.

(no matter the language 😉 of the columns)