Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Red errors in the results pane

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.


Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...