Finding dynamic SQL code that's not compatible with SQL 2005

  • Article

Upgrading a database from SQL Server 2000 to 2005 is not a difficult task. Microsoft provides the Upgrade Advisor tool to scan databases and report on SQL statements that will not execute in full compatibility code (9.0). But what about SQL code embedded in an application? What about dynamically generated SQL statements? The Upgrade Advisor is limited to what it can see inside the database. It is blind to the outside world.

There are a couple of ways to approach this problem. If you can dump all your SQL statements from your application code into a stored procedure then you can let Upgrade Advisor scan that for issues. But what about an application that has SQL code scattered here and there? What if your developers have written a zillion variations on a particular SQL statement (code reuse gone wild)? It's not going to be easy to hunt down all that code and put it into a stored procedure that will compile, much less be scannable by the Upgrade Advisor.

One possible solution is to use some of the other performance tools that are available. A combination of SQLNexus, the RML utilities and the Performance Stat Scripts will allow you to capture and derive a set of unique SQL statements from your application.

The steps are pretty straightforward:

  1. Install the Performance Stat Scripts on the database server used by your application.
  2. Set up your application's test utility or prepare a manual test plan to perform a full regression test on your application.Start the Performance Stat Script using the StartSQLDiagTrace.cmd file. This will capture a trace during the test.
  3. Run through your test plan.  It's important that you attempt to touch all your code base during this test.
  4. When the test is complete, stop the performance script operation on the server.
  5. Import the captured data using the SQLNexus utility into a SQL Server database.
  6. Use Query Analyzer to select all records from the tblUniqueBatches table in the SQLNexus database. Make sure you select the Results to Text option.
  7. Cut and paste the results into a new stored procedure.
  8. Run the Upgrade Advisor against this database and see if it finds any issues with your SQL statements.

Here's a sample query to extract data from the SQLNexus database:

SELECT '--' + normText
FROM [SQLNexus0422].[ReadTrace].[tblUniqueBatches]
WHERE LEFT(normtext,24) <> 'CREATE PROCEDURE SP_PERF'
AND LEFT(normtext, 3) <> 'SET'
AND LEFT(normtext, 2) <> 'IF'

This query filters extraneous statements including ones generated by the Performance Scripts process. You can modify the statement to suit your own needs. Also note that the results are generated as commented statements. You can remove this but you'll have to do a search and replace on the parameterized statements that ReadTrace generates to remove the {STR} and {##} parameters. If your application doesn't use a lot of dynamic SQL, you can change the source column in the query to OrigText. This will return the original query text without the parameters. Be aware that the OrigText column in this table will diplay the "exec sp_prepexec" instead of the actual SQL statement passed to that system stored procedure.

 Althought this method isn't perfect, I believe that a little tweaking will get you the results you need to get your database upgraded. One caveat: this method is only as good as the test method you use. There may still be some incompatibile code in your application. You should always run a full regression test in a test environment before deploying the changes to produciton.

The tools used are all listed in the Performance Tools links on this blog.

If anyone has a better method or has tried something similar, please post a comment.