April 25, 2011 at 3:10 pm
Hi there,
I have a query in SQL 2000 DB and I need to migrate it to SQL 2008 DB. It works fine in SQL2000 and I jāt need to revamp it into SQL2008. Below is the query in SQL2000. Please guide me how can we overload *= , =* clause in ON clause.
SELECT tblacc. *
FROM tblacc,
tblst,
tblreceipt,
tblrtemp,
tblitem
WHERE tblacc.rkey = tblreceipt.rkey
AND tblacc.stkey = tblst.stkey
AND tblacc.stkey *= tblrtemp.stkey
AND tblacc.stkey *= tblitem.stkey
AND tblacc.itkey *= tblitem.itkey
AND tblrtemp.rkey =* tblreceipt.rkey
April 25, 2011 at 3:20 pm
You need to turn them into left/right join mechanics, like so:
SELECT
tblacc.*
FROM
tblacc AS a
JOIN
tblreceipt AS r
ONa.rkey = r.rkey
JOIN
tblst AS l
ONa.stkey = l.stkey
LEFT JOIN
tblitem AS i
ONa.stkey = i.stkey
AND a.itkey = i.itkey
LEFT JOIN
tblrtemp AS rt
ONa.stkey = rt.stkey
AND r.rkey = rt.rkey
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
April 25, 2011 at 3:56 pm
It's a bit of a hack, and you still need to test, but you can use the Table DesignerEdit Query Designer in SSMS to convert queries for you. The majority of the time it is correct, but it's a client tool so there is no guarantee it will be right 100% of the time (refer to my comment earlier about testing). That said, you cuold just as easily make a mistake converting these by hand so if you have more than a few of these, or some non-trivial ones, it may save you some time by giving you a starting point.
1) Go to your new 2008 DB and change the compatibility level to 80 temporarily. ( [font="Courier New"]ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 80 ;[/font] )
2) Open the Table DesignerEdit Query Designer from any table.
2.1) Right click any table in Object Explorer and choose "Edit Top 200 Rows" to open the Table DesignerEdit Query Designer.
3) Show the SQL Pane in the Table DesignerEdit Query Designer by either clicking the "SQL" button on the tool bar or by right-clicking the grid and choosing Pane > SQL.
4) Now paste the old-style query containing *= and =* into the SQL Pane.
5) Now "Verify SQL Syntax" using the button on the toolbar or the context menu after right-clicking the SQL Pane text area.
6) Once you're done with all your query conversions don't forget to put your DB back into 100 mode ( [font="Courier New"]ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 100 ;[/font] ).
You will see that the old-style query has been converted to use the more current ANSI-style INNER and OUTER JOIN syntax. I used this method A LOT during a Sybase to SQL Server migration that included > 600 procs where many of them used the *= and =* join operators. The Table DesignerEdit Query Designer turned out to be right all but one time out of several hundred queries.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 12:39 pm
great! thts what i want. š
April 26, 2011 at 12:40 pm
thank you for your reply. it is a new tric.. I will try it.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply