Sql-server – Converting old SQL 2000 Non-Ansi code to SQL 2012 compatible

compatibility-leveljoin;sql serversql-server-2000upgrade

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?

Best Answer

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.

Related Question