May 14, 2009 at 5:18 am
Hello there, we are going to be upgrading several databases to SQL server 2008 soon, and after running Upgrade advisor, we have identified 50+ views and stored procedures that use old style operators for joins *= =* etc, this database is running in 8 compatibility mode explaining how it has been working up to now on a 2005 server. Either way, i think its time to get rid of these. However, some of the queries have 6+ tables, manually rewriting these joins is going to be a serious pain (and error prone) unless i can find a better way of converting these queries. For example, i have the following query:
SELECT PERIOD = CONVERT(CHAR, ( DATEPART(year, o.PUR_DATE) * 100 )
+ DATEPART(month, o.PUR_DATE)),
PRO_CODE = l.PRO_CODE,
OUTSTANDING = ( SUM(PUR_QTY_ORD) - SUM(PUR_TOT_REC) )
FROM PURCHASE p,
PUR_ORD o,
PUR_LINE l,
ANALYSIS_DATA s,
BALANCES_STOCK b,
COH_PUR_SPLITS u
WHERE p.PUR_CODE = o.PUR_CODE
AND l.PRO_CODE *= b.PRO_CODE
AND l.DEPOT_CODE *= b.DEPOT_CODE
AND PUR_QTY_ORD > 0
AND PUR_QTY_ORD > PUR_TOT_REC
AND o.DEPOT_CODE = l.DEPOT_CODE
AND s.PRO_CODE = l.PRO_CODE
AND o.DOC_ID = l.DOC_ID
AND o.DOC_TYPE = l.DOC_TYPE
AND p.PUR_CODE = '0099'
AND l.PUR_COMPLETE = 'N'
AND o.DOC_NUMBER = l.DOC_NUMBER
AND l.DEPOT_CODE *= u.DEPOT_CODE
AND l.DOC_TYPE *= u.DOC_TYPE
AND l.DOC_ID *= u.DOC_ID
AND l.DOC_NUMBER *= u.DOC_NUMBER
AND l.DOC_LINE *= u.DOC_LINE
GROUP BY CONVERT(CHAR, ( DATEPART(year, o.PUR_DATE) * 100 ) + DATEPART(month, o.PUR_DATE)),
l.PRO_CODE
I tried using Query Designer to fix them, however it only seems to be able to fix queries with 1 operator, Views with more than one join gives the following error:
Illegal use of outer join operator.
The text that you have selected cannot be represented graphically using the Query Designer. Please select just the text necessary to design a SELECT, DELETE, INSERT or UPDATE statement.
Any ideas/tools/scripts to convert these joins en-mass?
Any info would be great.
Regards
Jordon
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
May 14, 2009 at 6:41 am
I think this one will require application of the Mk1 Eyeball and the 19xx release of Brain.
There are too many variables possible for a script to automate the process. For example, which of the criteria should be moved to the JOIN and which should be left in the WHERE clause? You can't just assume that PK/FK relationship is the only criteria that defines the JOIN. You're going to have to rewrite the queries. But that's OK. You've got opportunities in there to clean up the code (I saw columns without a table reference and your tables don't define a schema) as well as fix the JOIN syntax.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply