SSMS reports incorrect line number for error that occurs in stored procedures - by Erland Sommarskog

Status : 

 


32
1
Sign in
to vote
ID 857794 Comments
Status Active Workarounds
Type Bug Repros 1
Opened 4/20/2014 2:47:44 PM
Access Restriction Public

Description

It seems that there is a change in SSMS 2014. Rather than displaying the raw line number reported from SQL Server, SSMS appears to add the offset of the line in the query window. This may be helpful in some situations, for instance when you a script with single statements.

However, when the error occurs in a stored procedure executed from the query window, this is very bad, because the message then points to the wrong line in the procedure. Yes, once you have learnt how the system works, you may be able work around it. But this is a breaking change, that will cause people a lot of gray hairs. If you wanted to make this improvement, you should have added this as an extra part to the message, or made this an option (which would have been off by default). 

It may seem that one idea would be to only make the adjustment when the message does not include a procedure name. However, the error may come from a batch of dynamic SQL invoked by a stored procedure.

This needs to be corrected at the first possible occasion.
Sign in to post a comment.
Posted by Ken [MSFT] on 5/23/2016 at 10:31 AM
Apologies for not updating this sooner. This issue has been fixed. If you download the latest version of SSMS (currently April 2016) you will now see the following in the repro included in this connect item:

Msg 220, Level 16, State 2, Procedure ScenesFromANightsDream, Line 2 [Batch Start Line 5]
Arithmetic overflow error for data type tinyint, value = 800.
The statement has been terminated.

The first line number will be the line number reported by the SQL Server engine within the given stored procedure where the error occured. The second line number is the starting line number of the batch currently being executed within the query window in SSMS. Unfortunatley SSMS cannot give the precise error line within that batch that made the call to the stored procedure. The batch starting line number should help narrorw down the call within large scripts though.
Posted by iamhers on 9/16/2014 at 12:01 PM
Isn't this feature showing us what the execution plan sees? In a case of recursion we get to see the total of lines in these, which really lets us see the errors? Although I would agree that any change should be configurable! And by virtue of compatibility it should operate the way it used to so nothing is broken by this.
Posted by Jos [MSFT] on 4/28/2014 at 10:06 AM
Thanks for reporting this issue. There seems to be something wrong with the line number reporting.
We are investigating.

--
Jos de Bruijn - SQL Server PM