Convert 80 compatibility t-sql to 90 compatibility

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert 80 compatibility t-sql to 90 compatibility

Author

Topic

gbnaveen
Starting Member

10 Posts

Posted - 2008-08-20 : 05:57:16

Hi,

The below code has been written in 80 compatibility mode (sql server 2000), since sql 2005 doesn't support 80 compatibility mode i need to convert this to 90 compatibility code(sql 2005).
i heard that =* (indicate right outer join in sql 2000) needs to be converted to right outer join.

from app2, app3, prodapp2, daysim, account, customer
where account.productid = prodapp2.productid
and app2.collected <= 0
and (app3.accountid =* app2.accountid
and app3.appmonth =* app2.appmonth
and app3.appyear =* app2.appyear)
and (daysim.daysIMMonth = app2.appmonth
and daysim.daysIMYear = app2.appyear)
and (app2.accountid = account.accountid
and account.accttype = '2')
and (account.custid = customer.custid
and customer.rollflag = 'Y')

Thanks in advance,
Naveen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 06:19:49

You are wrong.
Microsoft SQL Server 2005 does support Compatibility Level 80 (Microsoft SQL Server 2000).

With that said, Microsoft SQL Server 2005 does accept ANSI style joins.

from		app2
inner join	daysim ON daysim.daysIMMonth = app2.appmonth
			and daysim.daysIMYear = app2.appyear
inner join	account ON account.accountid = app2.accountid
			and account.accttype = '2'
inner join	prodapp2 on prodapp2.productid = account.productid
inner join	customer ON customer.custid = account.custid
			and customer.rollflag = 'Y'
left join	app3 on and app3.accountid = app2.accountid
			and app3.appmonth = app2.appmonth
			and app3.appyear = app2.appyear
where		app2.collected <= 0

E 12°55'05.25"
N 56°04'39.16"

gbnaveen
Starting Member

10 Posts

Posted - 2008-08-20 : 06:24:59

Peso Thanks for reply,

i was warned with the below message when i ran the Upgrade Advisor

"When you upgrade to SQL Server 2005, user databases maintain their compatibility mode. In 80 compatibility mode, the *= and =* operators for outer join are supported with a warning message. If you change the compatibility mode to 90, the operators are not supported and you should instead use the OUTER JOIN keyword."

thx
naveen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 12:35:05

Thats just telling you that sql server 2005 will not support *=,=* ...syntaxes from compatibility level 90 onwards so you should use ANSI style joins as Peso suggested as much as possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources