When you run scripts in SSMS and get an error, the error references a line number in the t-sql statement that caused the error. If you double click the error, SSMS will take you to the error. This works even if you have multiple statements in your query window. So it can be a really helpful feature. But if the error is in code that isn’t in the query window (for instance in a referenced stored procedure), nothing happens when you double click. In that case it can be good to know how SSMS calculates the line number in error messages.
Let’s experiment with this a bit. First a simple example:
If you run the same statement again but select the statement and the empty row above you get:
The line number of the error message is relative to the selected line numbers. In the query window the line number is 9, but the error message still references line number 2:
Also, the error message line number references the DML statement that contains the error, not the actual line number (column in this case) where the error is. This statement will generate an error that indicates the row number for the SELECT statement, not the “1/0” column.
When you create an object, for instance a stored procedure, if there are any leading empty rows, they will be included in the stored object:
If you, when debugging, script the stored procedure with sp_helptext, note that you need to remove the first two rows to get the line numbers correct in the script window:
If you instead generate the script with Management Studio, the USE *dbname* statements and the settings for ANSI_NULLS and QUOTED_IDENTIFIER are added automatically. Remove these statements (9 rows) to get the line numbers correct in the script window:
Further reading on error messages and line numbers:
Display Line Numbers in a SQL Server Management Studio Query Window (MSSQLTips).
Finding SQL Server Code Errors using Query Analyzer (MSSQLTips).
