Wednesday, July 18, 2007

Debugging T-SQL

In order to debug SQL many people open new windows in Query Analyser or Management Studio trying to see where the errors come from and opening transactions and rolling them back and basically be miserable.

Yet, even from Microsoft SQL 2000 stored procedures had debug support. You would use Query Analyser, open Object Browser, right click a stored procedure and select Debug.

However, in SQL 2005 you can't do that anymore. Query Analyser is no longer available, the Management Studio doesn't have debug options and the SQL 2000 Query Analyser doesn't allow you to debug stored procedures on SQL 2005 servers. But there is support for SQL debugging in Visual Studio .NET, in the Professional and Team versions. Let me rephrase: If you have the Express or Standard editions you are out of luck. No SQL 2005 debugging for you. I did some queries on the web searching for third party sql debuggers, maybe something from Microsoft, like their Javascript Debugger (which works better than the in-built javascript debugging in Visual Studio, btw)

There are some ugly problems that may occur:

Maybe others. In this case, please let me know so I can update the post. Other people need help too, you know?

Even so, SQL debugging is not as straight forward as usual debugging. From the Microsoft entry on How to debug stored procedures in Visual Studio .NET I quote the Limitations of stored procedure debugging:
  • You cannot "break" execution.
  • You cannot "edit and continue."
  • You cannot change the order of statement execution.
  • Although you can change the value of variables, your changes may not take effect because the variable values are cached.
  • Output from the SQL PRINT statement is not displayed