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.