SQL Server – How to Manipulate SQL Statement Before Execution

migrationsql serversql-server-2000sql-server-2008-r2

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 JOINS. But we cannot afford the modification of thousand .exe and .dll. So we cannot update our source code; there are too many projects.

What I want to accomplish, in SQL Server 2008 R2, is the following:

  1. Catch the SQL statement before its execution ?
  2. Update the statement (i.e., replace *= by LEFT OUTER JOIN) ?
  3. Give back the modified statement to the SQL engine for execution ?

This artifice is really not advised, but it will allow us to set the compatibility level to 100 event to our program still use the old syntax.

Thanks

I posted the same question on stackoverflow before I found this specific site.
https://stackoverflow.com/questions/27320554/manipulate-sql-statement-before-execution-on-server

Best Answer

First I will just summarize my answer to this same question posted on Stack Overflow:

  1. No, it is not possible to intercept a query request. (please see UPDATE section at the end for link to feature request)

  2. Even if it were possible, what you are requesting ignores the complexity of what is considered valid SQL. The variations on a simple SELECT * FROM sys.objects increases dramatically when considering spacing, returns, [ and ], table aliases, table hints, query hints, etc. Now just add a WHERE clause and throw in some functions, etc. In the end you would break more than you would be fixing.

Next, let's consider the meaning of this statement from the question:

we cannot afford the modification of thousand .exe and .dll

While "afford" can be expressed in absolute terms, it is most often relative compared to alternatives. So when considering what can be "afforded", you need to consider the following:

  1. How much of the current code base is actually affected? Do all of the thousands of files contain outer joins? Or is the real scope of work a bit smaller than "everything"?

  2. If you can make use of Compatibility Level 80, is that being viewed as:

    • "Ok, we done!", or more along the lines of
    • "We bought ourselves a couple of years before 100% of our code needs to be updated, so let's start immediately converting sections at a time with the goal of being 100% converted within 12 - 14 months"

    Because, if it is "Ok, we are done" then you are still in trouble.

  3. Outer Join syntax aside, have you looked through all of the changes (specifically removed functionality and changed behaviors) in at least SQL Server 2005, 2008, and 2008 R2? There have been A LOT and I have posted links at the bottom to the MSDN pages that detail those changes. Even if Compatibility Level 80 on 2008 R2 let's you use both old-style Outer Join syntax as well as Mirroring, you still have to run a full regression of all of your code because there are so many things that have changed. You might easily find that you have to update some amount of source code anyway due to changes that are not protected via Compatibility Level 80. Meaning, the focus of this question being on just Outer Join syntax is far too narrow (unless you have already done the analysis and found that to be the only issue).

I completely understand that it is a lot of work and the preference is to not do it if it's not necessary. But in all likelihood it is actually necessary. So your best bet is to develop a plan of attack regarding:

  • analyzing what code is affected by any of the changes listed in those linked pages
  • dividing the changes into groups by functional areas to make better use of QA time
  • prioritization

What has changed (besides removal of *=, =*, and *=* syntax) since SQL Server 2000:

A final (and minor) point to consider:

What do you gain if you get what you are asking for (i.e. being able to use SQL Server 2008 R2 with one or more databases set to Compatibility Level 80):

  • You didn't have to update code
  • You are able to use Mirroring

But, you are still stuck on SQL Server 2008 R2. We are weeks away from 2015 and there could be another version of SQL Server in a year or two. That leaves you in a similar position to the current one of being on a rather old/obsolete version. The implications of that are:

  • You could be having this conversation again in a few years
  • In the meantime, you are still stuck with the feature set that is currently two versions behind; you can't use any of the nice features that were added in the 2012 and 2014 versions.
  • Finding help will fall more to forums than being able to find qualified personnel. This might not be an issue for your company, but maybe for others who are reading this because they are in a similar situation. This is a non-technical issue, but one that, like the discussion of how is "affordable" defined, is more on the business side. For companies that have dedicated database developers/admins, finding those willing to work on such outdated versions is not easy. Again, a minor point, but still a point that should not be overlooked.

Hence, what is in your company's best interest is to:

  • Upgrade to SQL Server 2014 (and forget about 2008 R2)
  • Update the source code

UPDATE

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

Related Question