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?

1
  • We don't want to keep SQL 2000 server on Windows 2003 alive anymore. We want to decommission them.
    – Roni Vered
    Apr 22, 2016 at 12:25

3 Answers 3

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.

3
  • 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, 2016 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. Apr 22, 2016 at 12:46
  • @RoniVered Ah! My bad, I didn't see that. Sorry. Apr 22, 2016 at 14:09
-1

I recently started using OPENAI's playground to casually do things while I write code, and one of the things I did was try to ask OpenAI to convert my old query to new JOIN syntax, and it does that pretty well and has converted some long queries with so many joins to new syntax in a matter of seconds. I keep the old query in the ssms and check results with old and new to make sure the results produced are the same. What you do is goto openai.com and register, its free, but requires your phone number. You get a huge number of tokens (its just a currency for how openai processes text) for free for like 4 months, its enough to do a lot of work trust me. But you also have to try Codex, its their AI model specifically trained for programming, you have to apply for it and you will get access in like 1 or 2 days, its also free. I have noticed Codex produces better conversions then the normal non-Codex models.

I am just recommending all this because in my opinion there is nothing out there on internet that will do these kinds of conversions like the AI is now doing in 2022, you must try these, its the new way of doing work now. hope this helps someone!

-2

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 and acknowledge you have read our privacy policy.

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