1

We have a legacy application that is only used for a read only reference.

It is using SQL Server 2000. We want to migrate its database to SQL version 2012, however we can't upgrade the software and the vendor doesn't exist anymore.

The problem is that the views/stored procedures on the database are using old NON-ANSI SQL code that isn't supported anymore.

When we tried to simply restore it on SQL 2008R2 (as there isn't a direct restore from 2000 to 2012), and increase the compatibility level to 100, we got validation errors on the views/stored procedures, as they're using old join syntax.

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.

As there are ~200 objects that need to be updated with a newer and proper join syntax code, I was wondering whether a tool/script/code-generator exists somewhere that will help me convert the code.

I did notice that if I right click the view object and select Design, the query of the view IS converted to the proper syntax. But that isn't automated enough for me.

Any thoughts?

  • This is purely anecdotal, but I once got a temp contract job as a SQL Developer. When I started, they had exactly this requirement, and I found myself manually retyping ~300 stored procs into newer syntax from SQL 2000 because it was faster and more reliable than any automated or scripted process I could find. Good luck! – Molenpad Apr 22 '16 at 10:38
  • If it really is used only for read-only reference, I wouldn't convert it; virtualize the hardware if you haven't already; there's always the chance the conversion will introduce errors in an app that's been trusted for many years. – SqlACID Apr 22 '16 at 10:40
  • We don't want to keep SQL 2000 server on Windows 2003 alive anymore. We want to decommission them. – Roni Vered Apr 22 '16 at 12:25
3

The best match to your requirement that I can think of is the Query Designer in SSMS.

You can open the designer by right-clicking any table in your database and selecting "edit top 200 rows".

edit top 200 rows

Then, open the SQL pane by clicking the button in the toolbar:

SQL pane

Now you can paste your statement in the SQL pane and use the syntax checker to convert the code:

Syntax checker

The converted code goes like this:

converted code

As you may have noticed, many things are wrong here:

  • all formatting is gone
  • wildcards are expanded. This may actually be a good thing.
  • Unnamed expressions are named with wonderful random names such as "Expr1".
  • The syntax checker may rewrite your query in ways that don't match the initial code (see this connect item, for instance)

This method (and any other automated method) is not 100% bulletproof and can be used as a starting point to help you in the process, but it definitely needs manual intervention and extensive tests on the converted code.

  • Thanks for the elaborative response. However, I did mentioned in my question that I noticed that the Query Design on the views transform the code. Indeed sometimes it flips between LEFT/RIGHT join. However, I would be happy to make it even more automated, as we have over 200 objects that needs to be rewritten. – Roni Vered Apr 22 '16 at 12:28
  • @RoniVered it's probably worth diffing the execution plan XML for "before" and "after" versions as well. If they are the same except statement text then all's good otherwise maybe things need more validation. – Martin Smith Apr 22 '16 at 12:46
  • @RoniVered Ah! My bad, I didn't see that. Sorry. – spaghettidba Apr 22 '16 at 14:09
-1

I had a similar problem, but I managed to fix it using the SSMS inbuilt Query Designer Tool.

From my answer to a related question on Stack Overflow:

It simply refactored all my queries and put in the correct Join, Either Left or Right, and the Where condition as an AND condition on the Join itself, so I was getting the correct data result set for both pre and post, only sometimes the data sorting/ordering was a little off.

Still a little bit unsure though why the ordering/sorting was a little off if the Join condition was the same and the filters as well, because data was a 100 % match.

To get the query Designer to Work , just select your legacy SQL, and open the Query Designer by pressing Ctrl + Shift + Q or Goto Main Menu ToolBar => Query => Design Query in Editor.

That's it. You will get the converted query with the new Joins that you can copy and test. Worked 100% of the time for me, except in some cases where the sorting was not matching, which you can check by adding a simple order by clause to both pre and post to compare the data.

For reference, I cross checked with Using the Query Designer to convert non-ANSI joins to ANSI by John Paul Cook.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.