sql server 2005 join notation

Question

sql server 2005 join notation

pandeesh

Say Hey Kid

Points: 673
More actions
June 9, 2012 at 9:41 am

#392470

Hi,
Actually i tried to use the 2005 join notation in sql server 2012. i have set the compatible level to 90. But still i am getting the error:
Incorrect syntax near '*='.
use master
create table A(a int,b varchar(10))
insert A values(1,'a')
create table B(a int,b varchar(10))
insert B values(2,'a')
ALTER DATABASE master set COMPATIBILITY_LEVEL=90
exec sp_dbcmptlevel @dbname=master,@new_cmptlevel=90
select * from A a,B b where a.a*=*b.b
please let me know how to achieve this.
thanks in advance
Thanks,
Pandeeswaran

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The ANSI-89 style outer joins were depreciated and is no longer supported in SQL Server 2005 and later. For OUTER JOINS you need to use the ANSI-92 Style.

This would look like the

WHERE

dbo.Table1 t1

LEFT OUTER JOIN dbo.Table2 t2

ON (t1.Col1 = t2.Col1)

pandeesh Say Hey Kid Points: 673 More actions · Answer 2

Is it possible to simulate ANSI 89 notation in the current SQL server 2012?

Thanks,
Pandeeswaran

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

pandeesh (6/9/2012)
Is it possible to simulate ANSI 89 notation in the current SQL server 2012?

No. There is no way to simulate the ANSI-89 style. The best thing I can suggest is to embrace using the ANSI-92 style, not only for OUTER joins, but for all JOINs.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

ANSI-89 style joins are supported in 80 compat mode, which is not an option in SQL 2012.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

pandeesh Say Hey Kid Points: 673 More actions · Answer 5

Is 80 compat mode available in SQL server 2008?

Thanks,
Pandeeswaran

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Yes.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

Just a suggestion, start rewriting all the code now. You might be able to limp along on one version with compatibility support, but that's extremely limiting and, in the long term, completely unsustainable.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

I'll second that comment. If you have to use SQL 2008R2 as a crutch, so be it, but it is clear the way Microsoft has shifted in terms of the syntax they want to support.

Here is a cheat built into SSMS that may help for you get started updating your ANSI-89 queries:

http://www.sqlservercentral.com/Forums/FindPost1267778.aspx

Please read the whole thread and take the comments and cautions regarding testing very seriously.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato