Most DBAs have seen those nice clear red errors in the results pane. And I’m sure most DBAs have noticed the line number clearly displayed after the state.

Here is a fun test to try. Execute the following script:

PRINT 'test'
SELECT TOP 10 * FROM sys.databases
SELECT @@VERSION
DECLARE @var tinyint
SET @var = 12345678910
PRINT @var
SELECT OBJECT_NAME(12345)
SELECT 10/100

Now highlight only lines 4, 5 and 6 and click the execute button.

The first run gave you the following error:

Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type tinyint.

The second run gave you this error:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type tinyint.

If you look closely you’ll notice that the line number is different between the two executions. This is because in the second execution the batch only had three lines and the second had the error.

Now if you want to try something really interesting perform the second execution again. Once you have executed the batch click somewhere else on the query pane so that the three lines are no longer highlighted.

Now double-click on the red error in the results pain. … Go ahead. I’ll wait.

For those of you who don’t have the option (or inclination) to try it out I’ll tell you what happens. The line with the error, line 5, is now highlighted. Pretty cool hu?

Now there are some exceptions to this behavior. If the error is in a stored procedure, function, etc it won’t put you in the right place, and in fact the error line is the line inside the stored procedure, function etc. If the error is in a multi-line query it will sometimes work and sometimes will just put you at the top of the query. I’m not certain what the rules are there. I’ve added a few extra carriage returns and gotten it to put me on the error line, and sometimes I’ve reformatted my whole query and it still puts me on the top of it. Still, when it does work it’s very handy.