March 5, 2011 at 10:43 pm
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,
March 6, 2011 at 12:36 am
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 6, 2011 at 1:57 am
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".
March 6, 2011 at 9:44 am
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
March 6, 2011 at 10:08 am
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.
March 6, 2011 at 11:32 am
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
January 4, 2012 at 8:16 am
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.
January 4, 2012 at 9:20 am
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
January 4, 2012 at 9:22 am
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
January 4, 2012 at 7:36 pm
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
August 30, 2012 at 8:07 am
I have used the tool over and over to convert my SQL to the ANSI standard. However, it won't convert all of them. Any assistance with the following would be greatly appreciated!
SELECT *
FROM FacilityXref FX,
Facility,
#Type,
FacilityCode
WHERE ( FX.Id = FX.PointToId)
and ( FX.PointToId = Facility.Id)
and ( Facility.FacilityTypeId = #Type.Id )
AND ( Facility.PipelineId = IsNull( @PipelineId, Facility.PipelineId ) )
and ( Facility.Id *= FacilityCode.Id )
AND ( FacilityCode.AgencyId = IsNull( @AgencyId, FacilityCode.AgencyId ) )
SELECT *
FROM #TEMP Facility,
Facility Pipeline,
Agency
WHERE ( Facility.AgencyId *= Agency.Id )
AND ( Facility.PipelineId *= Pipeline.Id )
August 30, 2012 at 9:10 am
Here's a partial. You should be able to do the rest
SELECT *
FROM FacilityXref FX,
inner join Facility
on FX.PointToId = Facility.Id
, #Type, FacilityCode
WHERE ( FX.Id = FX.PointToId)
and ( Facility.FacilityTypeId = #Type.Id )
AND ( Facility.PipelineId = IsNull( @PipelineId, Facility.PipelineId ) )
and ( Facility.Id *= FacilityCode.Id )
AND ( FacilityCode.AgencyId = IsNull( @AgencyId, FacilityCode.AgencyId ) )
August 30, 2012 at 9:19 am
Thanks for the response!
I should have been more specific with my topic. The outer joins( *= ) are where I'm having troubles.
Thanks...
August 30, 2012 at 11:06 am
Steve Jones - SSC Editor (8/30/2012)
Here's a partial. You should be able to do the rest
SELECT *
FROM FacilityXref FX,
inner join Facility
on FX.PointToId = Facility.Id
left outer join FacilityCode
on Facility.Id = FacilityCode.Id
, #Type, FacilityCode
WHERE ( FX.Id = FX.PointToId)
and ( Facility.FacilityTypeId = #Type.Id )
AND ( Facility.PipelineId = IsNull( @PipelineId, Facility.PipelineId ) )
AND ( FacilityCode.AgencyId = IsNull( @AgencyId, FacilityCode.AgencyId ) )
August 30, 2012 at 12:44 pm
I appreciate the help.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply