In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again.
Coming from a .NET background, I'm used to receiving relatively helpful error messages that point me to the precise location of the error in my code:
Pinpoint error finding
SQL Server Management Studio does a decent job too - **except when it doesn't. ** Different versions of SSMS provide error line numbers differently.
Watch this week's video on YouTube
In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query. This means if you have a window full of T-SQL batch statements, your error message will be relative to the last "GO" in the editor before the syntax error:
In SSMS 2014, batch start locations aren't considered:
Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:
While all of these error messages point me in the right direction, I'm too lazy to try and remember how each version of SSMS refers to the error location. This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.
There is one feature that works consistently though that makes navigating to the specific error lines easily.
You can double click on the error message to be taken directly to the line number referenced!
I know this sounds simple, but it's one of those small things I never thought of trying until I saw someone else do it. Little did I know what I was missing out on - especially in those giant hundreds/thousands of line long scripts. but now that I know about it it's the only way I locate my query errors.
This functionality also works if you have multiple errors listed in the Messages window.
But what about if your SQL query is all on one giant line (like if it was copied from somewhere or generated dynamically)? You can use a regular expression to format your query first and then click on the error message to quickly navigate to the correct lines.