1

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

4

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.

  • @podosta Thanks. I just added some additional thoughts below the section of links. Also, I am curious if you looked through those links and found other changes that will have to be accounted for in your code. I remember years ago when the company I was at upgrade from 2000 to 2005 and we thought it was just a few things to fix but found in testing that it was actually several. Fortunately the changes between versions past 2005 has not been as dramatic as between 2000 and 2005. – Solomon Rutzky Dec 12 '14 at 16:33
4

So you need to make a choice:

  1. Change to Compatibility Level = 100 and break your code.
  2. Set Compatibility Level = 80 and your code will live a little longer.
  3. Find some way to globally edit your code, dynamically or statically, so that you can switch to Compatibility Level = 100.

It would be interesting to understand why you want Level = 100 when you do not want the code to change.

If you install SQL Server 2008 R2 the SQL Server features available for the system (such as compressed backups) will work without regard to any database's compatibility level. So, keep your database (a little while longer) in compatibility level 80.

EDIT: Responding to your comment on Mirroring. Mirroring is a server feature first introduced (for production use) with SQL Server 2005 SP1. Since this is a feature of the server, it will work for all compatibility levels running on your SQL Server 2008 R2 server.

Therefore, keep your compatibility level at 80 (SQL Server 2000) level and use mirroring on the databases where you need it.

There is a lot of documentation on mirroring, but since this is new to you, you should start by reading Ron Talmage's white paper at: http://technet.microsoft.com/en-us/library/cc917680.aspx

Please note, however, that SQL Server 2008 R2 is the last version to support SQL Server 2000 (80) compatibility. And SQL Server 2008 R2 is no longer under mainstream support since 7/8/2014, though the limited extended support will continue into 2019.

This means that for your SQL Server next upgrade you must upgrade your T-SQL code.

END OF EDIT

Since you mention DDLs and EXEs, you seem to be indicating that your SQL queries are in the client not existing as stored procedures, functions, etc in the SQL Server. If you decide to write a wrapper code for use in your EXEs and DLLs and include it in all the locations. That sounds as disruptive as just changing the SQL Queries.

Since you are using the *= syntax your code should look like this:

SELECT a.IdColumn, b.Description
   FROM EntityMaster a, EntityDescription b 
   WHERE a.IdColumn *= b.IdColumn

So, there is more than a single edit needed to change the code to:

SELECT a.IdColumn, b.Description
   FROM EntityMaster a LEFT OUTER JOIN EntityDescription b 
        ON a.IdColumn = b.IdColumn

And I expect you have much more complex code than that.

  • We want a Level = 100 in order to use the mirroring feature of SQL server between two sites. (can we use it with compatibility 80 ?) – podosta Dec 6 '14 at 21:14
  • Sure you can mirror a compatibility 80 database. Mirroring is a server feature, not a T-SQL feature. Will update the answer. – RLF Dec 6 '14 at 21:51
  • SQL Server 2008 R2 SP3 is under mainstream support until 2015 since they just released SP3 (normal 12 month span for an SP). – user507 Dec 7 '14 at 1:22
  • blogs.msdn.com/b/data-platform October 1 post says otherwise, I believe – RLF Dec 7 '14 at 4:07
  • RTM build is out of mainstream support. Service Packs come with 12 month support from the release date. – user507 Dec 7 '14 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.