Search code examples
sqlsql-serversql-server-2008-r2sql-server-2000

Manipulate SQL statement before execution on server

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)

Solution

  • No. That would require two things that do not exist:

    1. Something akin to an INSTEAD OF SELECT trigger

      and

    2. 1000 billable hours (or more depending on how good you and/or your team is at QA) to come up with the text parsing / regular expressions / etc needed to capture all of the syntax variations that are allowed in queries: block comments in the middle of values , inline comments at the end, conditions broken up into multiple lines, table aliases, outer joins of inner-joined tables, and so on and so on.

    You will actually spend less time updating your source code (as others have suggested), even across "thousand exe and dll", than you will trying to implement this and then spending the rest of your life debugging it and taking support calls for problems that eventually turn out to be caused by bugs in this.

    (P.S. this answer has been cross-posted on DBA.StackExchange, but with a bit more detail there)

    UPDATE

    I submitted a Microsoft Feedback Suggestion for this ability: Intercept query batch from client to rewrite it or cancel it.