May 19, 2008 at 7:01 am
One thing you might try is to open up the query in SSMS, highlight the entire query, click on the Query menu and select Design Query in Editor. SSMS will re-write the statement in proper ANSI 92, taking any guesswork out of it.
With that said, if you are referencing any variables in the query, you'll need to remove those references before this will work. This is how I re-wrote most of mine.
May 19, 2008 at 7:03 am
ALZDBA (5/17/2008)
keep in mind the "old" syntax (*= / =*) has been anounced for deprecation since SQL7.0 sp2.
Maybe but developers won't stop using it until they are forced to by the engine not supporting it. Even then, the developer may just keep you in the past. We still have to run our database in mode 80 because the developer (vendor) has not fully re-written their code to support mode 90 (ANSI 92).
May 19, 2008 at 7:41 am
...(vendor)...
Not much you can do about it, but complain :ermm: ......
It would be better to have your companies "software-client" complain and request a sql2005 certificate for it.
Let them reside on sql2000 until the software supports sql2005.
Compatibility mode is a nice thing, but will it cover 100% :crazy:
You, as a dba, will not have the full envelope of sql2005 stuff available to monitor it. (e.g. performance dashbord)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 23, 2008 at 4:51 pm
It's supposed to have the SQL Server 2K5 Full Capabilities talking about performance because what you do when you change compatibility mode is just to use the ANSI SQL 89 Standard and not the 92 which is the new standard and the one used with compitibiliy 90 mode. You don't change your database engine with compatibility, but yeah there are features, like the pivot sentence, that you will have to find a way to work around. I also read that you can use all the capabilities but you would only need to put the 2k5 reserved words between [] or "" I don't remenber but you are "supposed" to be able to still work with them 😀
May 24, 2008 at 1:39 am
jim.powers (5/15/2008)
select col1,col2 from table1,table2
where table1.col1*=table2.col2
and table1.col4=table2.col4
This one would be:
select col1,col2
from table1
inner join table2 as innertable2 on table1.col4 = innertable2.col4
left outer join table2 as outertable2 on table1.col1 = outertable2.col2
Fortunately, I never had to deal with old syntax. The ansi equivalent would not work, however, as you have Col2 in outertable2 and innertable2.
So, the query must be either inner or outer join, not combined.
I'd write it as:
select col1,col2 from table1 left outer join table2
on table1.col1=table2.col2 --#1
and table1.col4=table2.col4 --#2
where table2.col4 is not null --#2 part 2
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply