SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL 2000 to 2008


T-SQL 2000 to 2008

Author
Message
Anushka Udayanga Ruwan...
Anushka Udayanga Ruwan Pathirana
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 67
Hi,.

Hi I am having around 200-250 reports that are developed in SQL 2000 and i have used *= , =* many of places. So recently we are migrated to SQL 2008 and All reports I have to fixed for the 2008 standard.
So I have couple of issue in compatibility like

"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, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. 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."

So It takes long time to re-query all things and fixing. Even we dont need to enable backword compatibility of 2008, we j't need to fixed all queries...

SO my point is, DO we have any tool or something that fix all queries eaily.

Thanks and Best Regards,
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20785 Visits: 7660
Nope, not really.

Your best bet is to tag a local recruiter, get yourself a Junior techie on the cheap for a month or two, and have them siphon through the T-SQL. They'll be happy for the experience and your boss will be happy to not be wasting a FTE's time.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23899 Visits: 13559
What you could do is to search for the character string to be replaced automatically so at least you'll know what definitions you have to tackle. Search the script section in this site for "find string in code".



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Jim Murphy
Jim Murphy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1265
I was thinking along the lines of lutz. But since this will be slightly harder than a search and replace, try a tool like red gate's SQL search.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147327 Visits: 19434
SQLSearch will help you find these, but it can't help you fix them.

The issue is that it's not necessarily easy to determine what needs to be moved from the WHERE clause to the ON clause. IF you have multiple fields joining these tables, it could be that you do not want all of these conditions in the ON clause.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jim Murphy
Jim Murphy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1265
Right. I should have clarified that rather than understate the rework. Could have saved you the time of posting more clarity.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
randren
randren
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Yes, you do have a tool for fixing queries. Not automatically everything all at once in your system, but for one query at a time, the "design query in editor" tool will do the conversion in most cases.

There are several ways to get to this tool, but I usually just paste my offending query (with a *= or =*) into sql query analyzer. I highlight the complete query. Then click ctrl + shift + q. This will open the query design tool, with the entire query rewritten using ansi join operators.

I usually just copy the from and where clauses from there, and paste them back into my source. So far, it's working great.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)SSC Guru (147K reputation)

Group: Administrators
Points: 147327 Visits: 19434
randren (1/4/2012)
Yes, you do have a tool for fixing queries. Not automatically everything all at once in your system, but for one query at a time, the "design query in editor" tool will do the conversion in most cases.

There are several ways to get to this tool, but I usually just paste my offending query (with a *= or =*) into sql query analyzer. I highlight the complete query. Then click ctrl + shift + q. This will open the query design tool, with the entire query rewritten using ansi join operators.

I usually just copy the from and where clauses from there, and paste them back into my source. So far, it's working great.


That's a nice trick

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67947 Visits: 9671
Steve Jones - SSC Editor (1/4/2012)
randren (1/4/2012)
Yes, you do have a tool for fixing queries. Not automatically everything all at once in your system, but for one query at a time, the "design query in editor" tool will do the conversion in most cases.

There are several ways to get to this tool, but I usually just paste my offending query (with a *= or =*) into sql query analyzer. I highlight the complete query. Then click ctrl + shift + q. This will open the query design tool, with the entire query rewritten using ansi join operators.

I usually just copy the from and where clauses from there, and paste them back into my source. So far, it's working great.


That's a nice trick


+1
Jim Murphy
Jim Murphy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 1265
randren (1/4/2012)
Yes, you do have a tool for fixing queries. Not automatically everything all at once in your system, but for one query at a time, the "design query in editor" tool will do the conversion in most cases.

There are several ways to get to this tool, but I usually just paste my offending query (with a *= or =*) into sql query analyzer. I highlight the complete query. Then click ctrl + shift + q. This will open the query design tool, with the entire query rewritten using ansi join operators.

I usually just copy the from and where clauses from there, and paste them back into my source. So far, it's working great.


Huh. I'll have to try that. Sounds useful. Thanks for sharing that.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search