Debugging in SQL Management Studio

First off, let me start by saying that I absolutely despise the Microsoft SQL Server 2005 Management Studio. I find it anemic, difficult to use and poorly designed. Unfortunately, there are few alternatives (or at least that I’ve found) for those of us who develop using SQL Server 2005.

One common frustration is trying to debug SQL queries using the built-in editor. The database will kindly inform you of any errors when you attempt to execute a query, and also provide a line number. But that’s about it.

Compared to the error highlighting features available in Visual Studio,  its hard to imagine that Management Studio is developed by the same company.

For example take the following error:

Example of Sql Error

The editor informs me that I have two errors, one on Line 4 and another on Line 2. But here’s the thing – if I go to line 2 or 4 of my script, I find nothing pertaining to the error. So what’s going on?

The query parser conveniently restarts counting line numbers whenever it encounters a code block, so anything in between a BEGIN and END statement, starts at line 0 again. If you have multiple code blocks in a script, then line numbers are completely useless.

So how the heck do you find out which line is actually causing the error?

In the most poorly designed way ever imagined,  Microsoft has graciously decided to help you out by allowing you to double click on those error messages and be taken to the exact part of your code that is causing the error.

Whoa …double click, eh? And what’s to suggest that those unfriendly error messages in cryptic 10pt courier red are clickable? Your guess is as good as mine, but they are.  I guess even changing the mouse cursor to a hand with a pointy finger – was too much work for the person who developed the Messages window in Management Studio.

And then that leads one to wonder – if there is some way for this double-click function to map the error message to the actually line number in the editor, then why isn’t this function used to display the “real” line numbers for error messages, as opposed to the completely useless line numbers that are shown now?

Considering the amount of money organizations spend on SQL Server, I find this type of design incomprehensible and short-sighted,  especially in an enterprise-level product.