The Fastest Way To Locate Errors In Your SQL Query

, 2018-01-30

Photo by N. on Unsplash

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 episode 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.

Thanks for reading. You might also enjoy following me on Twitter.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads