Red errors in the results pane

3

December 19, 2012 by Kenneth Fisher

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.

About these ads

3 thoughts on “Red errors in the results pane

  1. [...] Kenneth Fisher is showing us red errors in the results pane. [...]

  2. Thierry Van Durme says:

    Nice. Maybe has something to do with what is in memory?
    Hehe, there’s a funny typo btw… the results pain :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 428 other followers

%d bloggers like this: