March 18, 2008 at 5:30 am
i am converting DB from sql 2000 to sql 2005.in my DB only one qry has *=(left outer join) syntax. i compiled this SP in sql 2005 , it compiled successfully also executed separately with *= and modified that to left outer join both results same. will it make any harm if it be like sql 2000 syntax (*=).
March 18, 2008 at 5:47 am
Yes, but the compatibility level will have to stay below 90. This syntax will be depreciated in future.
create table t1(a int)
create table t2(b int)
go
select a from t1, t2 where t1.a *= t2.b
go
drop table t1
drop table t2
Msg 4147, Level 15, State 1, Line 1
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.
Piotr
...and your only reply is slàinte mhath
March 18, 2008 at 6:00 am
March 18, 2008 at 6:10 am
Thank you ...
can u say how to find the current compatibility level in database???
will it set for each database or server level ?
March 18, 2008 at 7:03 am
You can set compatibility level per database. You have migrated this database from SQL 2000 so its compatibility level stayed at 80. You can change it using sp_dbcmptlevel stored proc. And here's list of all databases and their compatibility levels.
select name, compatibility_level from sys.databases
Piotr
...and your only reply is slàinte mhath
March 18, 2008 at 7:13 am
For what it's worth - those types of joins are set to not work in 2008. So the "future version" isn't so future at all.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 19, 2008 at 8:03 am
I believe it was a depricated feature in SQL Server 7.0 if my memory serves me right. It was recommended that you don't use that syntax and to use the ANSI joins. Hence, it make sense that SQL Server 2008 would get rid of that feature.
March 19, 2008 at 12:19 pm
niranjankumar_k,
Your "*=(left outer join)" syntax problem may not be your only issue regarding your SQL 2000 to SQL 2005 upgrade. If you have not already done so, I recommend that you download and install the Microsoft SQL Server 2005 Upgrade Advisor. It can be downloaded at:
Brief Description
Download the April 2006 version of Microsoft SQL Server 2005 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 to help you prepare for upgrades to SQL Server 2005.
It can be safely installed on a system with SQL Server 2000, and can be run against all databases in an instance, or a select subset of databases.
The Upgrade Advisor generates an expandable report that checks for a dizzying array of elements regarding an upgrade.
You may also want to review the SQL Server 2005 Upgrade Handbook. This document among other things covers usage of the Microsoft SQL Server 2005 Upgrade Advisor. It can be acquired here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Keep us posted on your findings and ugrade progress.
Hope This Helps,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply