SQL Server 2012 keyword exception

Question

The question that I already know about does not have a big answer.

I have a third-party application that I cannot change . The application database was converted from MS Access to SQL Server 2012. The program connects to ODBC and does not care about the backend. It sends a fairly direct SQL query, which seems to work on SQL Server too.

However, there is a problem with a single table that has the name "PLAN", which I already know is the SQL Server keyword.

I know that you usually refer to such a table with square brackets, but since I cannot change the SQL, I was wondering if there was any ugly hack that could either override the keyword or convert SQL to fly.

Answer 1

You can try to edit a third-party application using a hex editor. If you find PLAN lines, edit this to something like PPAN, and then rename the table, views, etc. If you catch everything, it might work. But of course this is an ugly thing.

Answer 2

I think you're spoiled, I'm afraid. The only other approaches that I could suggest are

• Intercepting network packets before they reach SQL Server, which is clearly quite difficult. See https://reverseengineering.stackexchange.com/questions/1617/server-side-query-interception-with-ms-sql-server and in particular the answer https://reverseengineering.stackexchange.com/a/1816

• Decompiling a program to change it if it is a Java or .NET application.

Answer 3

I suspect you want to. You could

• Connect a third-party application to the MS Access database, which uses linked tables , where the Access table is nothing more than a transition to the SQL Server base table. What do you want to do:

  • Change the names of the mismatched columns in the SQL Server schema.
  • Create linked tables in Access
  • Create a view / query set in access that has the same schema as a third-party application expects.

  
  Having done this, a third-party application should be able to say "Access SQL", as always. Access cares about "translating" into T-SQL. Life is good. I suspect you will take something from a performance hit as you proxy everything through Access, but I don’t think it will be huge.

  That would be my preferred solution.

• Another option is to write a “shim” DLL that implements the ODBC API and simply wraps the actual calls to the true ODBC driver. Then grab the queries and upgrade them as needed before calling the wrapped DLL method. The tricky part is that your third-party application may follow the columns in order or may follow them by the column name. Or a mixture. This means that you may need to convert the column names on the return path, which may be more complicated than it sounds.