Line numbers in t-sql error messages

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:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

If you run the same statement again but select the statement and the empty row above you get:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

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:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

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.

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

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:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

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:

CAPTURE131002_6

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:

SQL Server Management Studio - scripting of objects and line numbers

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

Leave a Reply

Required fields are marked *.