Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-SQL 2000 to 2008 Expand / Collapse
Author
Message
Posted Saturday, March 5, 2011 10:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 12:00 AM
Points: 14, 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,
Post #1073798
Posted Sunday, March 6, 2011 12:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 6,147, Visits: 7,204
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
Post #1073801
Posted Sunday, March 6, 2011 1:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 7,038, Visits: 12,952
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
Post #1073810
Posted Sunday, March 6, 2011 9:44 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:16 AM
Points: 650, Visits: 1,265
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
Post #1073853
Posted Sunday, March 6, 2011 10:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:53 PM
Points: 33,063, Visits: 15,179
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
Post #1073859
Posted Sunday, March 6, 2011 11:32 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:16 AM
Points: 650, Visits: 1,265
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
Post #1073874
Posted Wednesday, January 4, 2012 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 4, 2012 8:10 AM
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.
Post #1229978
Posted Wednesday, January 4, 2012 9:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:53 PM
Points: 33,063, Visits: 15,179
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
Post #1230048
Posted Wednesday, January 4, 2012 9:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,385, Visits: 9,602
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
Post #1230052
Posted Wednesday, January 4, 2012 7:36 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 7:16 AM
Points: 650, Visits: 1,265
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
Post #1230468
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse