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:
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:
- For example you want to add an SQL Connection to Visual Studio 2005 and you get a "Unable to add data connection. Key not valid for use in specified state." error. The fix is to delete all not working sql connections that are already defined.
- Or you cannot step into a stored procedure from ASP.NET code on Windows Server 2003 in which case you need to run the WWW service in IIS 5.0 isolation mode.
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
Comments
Be the first to post a comment