[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2005 Outer Joins and SQL Express

Posted on 2006-03-27
10
Medium Priority
?
1,110 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:purplesoup
  • 5
  • 3
9 Comments
 
LVL 10

Expert Comment

by:StephenCairns
ID: 16307465
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16307504
As I have just confirmed the only way you can use the old style is by setting the Compatibility Level to 80.
0
 

Author Comment

by:purplesoup
ID: 16308787
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 16313474
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16328650
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
 

Author Comment

by:purplesoup
ID: 16332852
What I want to know is "is there anything from Microsoft to say this is what will happen"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16339307
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
 

Author Comment

by:purplesoup
ID: 16340511
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16347638
>>Your reply not really answer this question<<
Fair enough.

Good luck.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question