Tip SSMS: How to quickly find the line with the Syntax error

  • Article

When executing code in SQL Server Management Studio. Occasionally people make errors. If you find yourself counting thru your TSQL Batch up to “LINE 231” where your error is supposed to be, then this tip is for you.

 

Tip: Double Click on the Error message in the results pane. (In diagram below, see Lines circled in Red) This should jump immediately to the line of code containing the error & highlight it. (see diagram below, Line circled in Green)

 image

9 times out of 10 is works perfectly. The other times the confusion is usually caused by SQL Engine having an issue with an earlier line of code. eg:

  • You left the “;” off the statement before a WITH statement. But the Error pointed to the line containing the WITH Statement. 
  • Your Statement has multiple lines & it is unable to distinguish the precise line that the error is located. (but you should be close)
  • Your “CREATE TABLE” statement failed. So all the SELECT statements that use it, die.
  • You are USEing the wrong database so all the objects aren’t there.

Versions supported: SQL 2005, SQL 2008, SQL 2008 R2. (From memory even worked in SQL2000)

I thought everyone knew this, so was surprised by the reaction at Tech-Ed. Hence the post.

Hope you found it useful

Dave

Technorati Tags: SQL Server,SQL Server Management Studio,TSQL