SQL Server 2005 Outer Joins and SQL Express

I understand from reading various developer forums that SQL Server 2005 has a major gotcha where the old outer join syntax is no longer supported by default, however there is a way to turn it back on again:

http://www.forta.com/blog/index.cfm/2006/1/15/SQL-Server-2005-Outer-Join-Gotcha

I only have SQL Express and I cannot see this problem - does it exist for SQL Express?
purplesoupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenCairnsCommented:
as it is a cut down of sql server 2005 I would say yes, it does not support the old syntax
but you have it installed, try it.
0
Anthony PerkinsCommented:
As I have just confirmed the only way you can use the old style is by setting the Compatibility Level to 80.
0
purplesoupAuthor Commented:
OK - I think I understand what has happened. When I got SQL Express I restored an old SQL 2000 database and ran with that.

This ran fine and I didn't have any outer join syntax problems (that is what I meant by "I cannot see this problem" - I did try it and it worked ok).

Running sp_dbcmptlevel dbname to check the compatibility level of the database, it is 80, whereas the compatibility level of the master database is 90. Hence I conclude that restoring SQL Server 2000 databases into SQL Server 2005 automatically sets the compatibility level to 80, which is why I did not get the outer join problem.

Can anyone confirm if this is the correct explanation (e.g. is there anything from Microsoft to say this is what will happen?)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
>>Can anyone confirm if this is the correct explanation<<
Yes.  Change your database compatibility to 90 and you will get the error message as I did, when I did that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
I believe I accurately answered your question, so please re-read the following EE Guidelines regarding EE grading standards and let me know if I missed anything:
What's the right grade to give?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

Also, this:
Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18
0
purplesoupAuthor Commented:
What I want to know is "is there anything from Microsoft to say this is what will happen"
0
Anthony PerkinsCommented:
Sure there is and has been since 1999.  Let me know, if you need the link.  Meantime can I have your comments regarding the grading on this thread.  Was there some way my answer was deficient?
0
purplesoupAuthor Commented:
Let me repeat myself again.

In my question I pointed out that when I restored a backed up database the compatibility level appeared to be different to the default compatibility level for SQL Server 2005.

"Running sp_dbcmptlevel dbname to check the compatibility level of the database, it is 80, whereas the compatibility level of the master database is 90. Hence I conclude that restoring SQL Server 2000 databases into SQL Server 2005 automatically sets the compatibility level to 80, which is why I did not get the outer join problem.

"Can anyone confirm if this is the correct explanation (e.g. is there anything from Microsoft to say this is what will happen?)"

Your reply not really answer this question - it didn't show a link to any documentation from Microsoft that this is supposed to happen. In fact re-reading it I am not clear how "Change your database compatibility to 90 and you will get the error message as I did, when I did that." explains why restoring a database changes the default compatibility level, so I will request for question to be re-opened.
0
Anthony PerkinsCommented:
>>Your reply not really answer this question<<
Fair enough.

Good luck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.