Manipulate SQL statement before execution on server

Question

Our databases are on SQL Server 2000. We are migrating to SQL Server 2008 R2.

We want to update the compatibility level to 100.

But the join operator *= and =* are no more supported with that compatibility level.

The advised solution is to update our source code and change the operators to OUTER JOIN.

But we cannot afford the modification of thousand exe and dll. So we cannot update our source code; there are too many projects.

My question is:

Is there in SQL Server 2008 R2 any system which will allow me to:

1. catch the SQL statement before it's execution
2. update the statement (for instance replace *= by LEFT OUTER JOIN)
3. give back the modified statement to the SQL Server engine for execution

This method would allow us to set the compatibility level to 100 even though our programs still use the old syntax

Thanks

(P.S. This question has been cross-posted on dba.stackexchange)

Answer 1

I agree that updating the apps would be the right thing to do, long-term.

But sometimes doing the right thing is not an option, and you have to consider alternatives.

In your case, there is the option to add a smart database proxy (e.g. Gallium Data) to rewrite the query as needed. You can define a filter in the proxy that will intercept the problematic queries and rewrite them as desired. Then all you need is to get those apps to talk to the proxy instead of directly to the database.

Given your problem, is this preferable to modifying all these apps? That's a decision you need to make, given your particular situation. But you have that option.