none
Debugging Stored Procedures shows wrong line pointers RRS feed

  • Question

  • Hello all,

    I have an extremely annoying problem when debugging stored procedures in SQL Server 2014 with SSDT or SSMS. When calling a SP thru EXECUTE in Debug mode, 9 out of 10 SPs are traced with a wrong yellow arrow-pointer to the line currently reached. The offset is between 6 to 15 lines downward. Tracing itself and update of the "Locals"-view works as expected. All SPs contain comments also before the Create Procedure statement. The SP shown when tracing show exactly the same content as the stored SQL in the SSDT project under work incl. Create procedure and all comments.

    The picture here show the first line selected after the debugger has traced into the SP. The first line really executed with "Next" will be SET NOCOUNT ON.

    Any idea to get rid of this annoying behavior? If this does not turns out as my fault and some of you would support that, I would like to post this to SQL Connect.


    Regards Jörg

    Sunday, April 26, 2015 3:55 PM

Answers

  • Hi Lydia,

    it's a long time ago that this flaw was posted here. It was also posted to connect by another user. Without any reaction after being tagged with "Under investigation".

    In between, after a little testing, I discovered, that comments are triggering this behavior. So placing comments after the first executable statement, fixes the problem.

    This seems so trivial but explains the fact that you could not reproduce it: MS developers tend not to comment their code, isn't it? ;-)

    • Proposed as answer by Jörg Debus II Monday, February 19, 2018 9:31 AM
    • Marked as answer by Jörg Debus Monday, February 19, 2018 3:21 PM
    Monday, February 19, 2018 9:30 AM
  • Based on your description, when calling a stored procedure through EXECUTE in Debug mode , the yellow pointer will stop on the " SET NOCOUNT ON?, right?

    As I read Jörg's post and screenshot, the pointer is showing one line, when execution is in fact on another.

    I did not try to repro this myself, as it was long ago I gave up using the debugger. I found that spent more time to get it working than I gained from it. PRINT and SELECT works well for me.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Jörg Debus Tuesday, April 28, 2015 5:13 PM
    Monday, April 27, 2015 9:46 PM

All replies

  • Hi Jörg,

    Based on your description, when calling a stored procedure through EXECUTE in Debug mode , the yellow pointer will not stop on the " SET NOCOUNT ON”, but stop on the line as your screenshot, right? If so, I make a test on my computer, however I cannot reproduce your scenario. 

    As far as I know, there is no method to get rid the debug behavior. If you concern about the debug behavior in SQL Server Management Studio , personally, I recommend you submit a feedback to the Microsoft Connect at this link https://connect.microsoft.com/SQLServer/Feedback  . Your feedback enables Microsoft to offer the best software and deliver superior services.

    Thanks,
    Lydia Zhang

    If you have any feedback on our support, please click here.


    Lydia Zhang
    TechNet Community Support







    Monday, April 27, 2015 3:38 AM
    Moderator
  • Based on your description, when calling a stored procedure through EXECUTE in Debug mode , the yellow pointer will stop on the " SET NOCOUNT ON?, right?

    As I read Jörg's post and screenshot, the pointer is showing one line, when execution is in fact on another.

    I did not try to repro this myself, as it was long ago I gave up using the debugger. I found that spent more time to get it working than I gained from it. PRINT and SELECT works well for me.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Jörg Debus Tuesday, April 28, 2015 5:13 PM
    Monday, April 27, 2015 9:46 PM
  • Based on your description, when calling a stored procedure through EXECUTE in Debug mode , the yellow pointer will stop on the " SET NOCOUNT ON?, right?

    As I read Jörg's post and screenshot, the pointer is showing one line, when execution is in fact on another.

    I did not try to repro this myself, as it was long ago I gave up using the debugger. I found that spent more time to get it working than I gained from it. PRINT and SELECT works well for me.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi  Erland,

    Thanks for your information, I edited my reply.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Tuesday, April 28, 2015 1:37 AM
    Moderator
  • Hi Lydia,

    That's what happens here.

    But the problem is more general: The line selected with the yellow pointer is not the first executable statement but one 7 to 10 line downward the source. When continuing with tracing the correct statement is executed and the yellow pointer is stepped to the source on the wrong lines till the end.

    I think it would be a good step to solve the problem and to encourage me to post it to Connect, if you could reproduce it at MS on a SQL SERVER 2014 VS 2013 installation. I could easily send you my sources (< 300 LOC) to an upload link so that you can test it with my real life SQLs. Databases used are irrelevant as long as the source passes CREATE PROCEDURE checks, because the problem is visible immediately after entering the SP w/o any statement executed.

    And pls. keep in mind the problem shows up in a SSDT and a SSMS environments.


    Regards Jörg


    • Edited by Jörg Debus Tuesday, April 28, 2015 5:26 PM
    Tuesday, April 28, 2015 5:25 PM
  • This thread is almost 2 years old.  I get no relevant hits googling "sql debugger highlighting wrong lines" from the past year.  And yet, I'm using SSMS 2016, latest build, and I have the same problem.

    Really disappointing.  Even shocking.  Enterprise RDBMS solution and no reliable debugging.

    Considering the price of licenses these days, and the new update scheme of SSMS, this is pretty sad.


    Wednesday, February 1, 2017 7:31 PM
  • Hi Dwain,

    exactly. So Erland's response have said it all: Don't use SQL-Debug. Which is not that easy when developing CLR-SPs.

    In the meantime, I have found that this symptom disappears when the first line of  the SQLs is always a CREATE statement and not a comment. I discovered this when using functions and have not verified it with SPs.


    Regards Jörg

    Monday, February 6, 2017 10:29 AM
  • Not using the SQL Debugger is like working in Stone Age.

    I added an idea to Microsoft connect last year without any reaction about this annoying bug - see here (as Connect has been retired):

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32898118-debugging-stored-procedure-in-sql-management-studi

    So please vote up :-)

    Patrick

    Monday, February 19, 2018 9:00 AM
  • Hi Lydia,

    it's a long time ago that this flaw was posted here. It was also posted to connect by another user. Without any reaction after being tagged with "Under investigation".

    In between, after a little testing, I discovered, that comments are triggering this behavior. So placing comments after the first executable statement, fixes the problem.

    This seems so trivial but explains the fact that you could not reproduce it: MS developers tend not to comment their code, isn't it? ;-)

    • Proposed as answer by Jörg Debus II Monday, February 19, 2018 9:31 AM
    • Marked as answer by Jörg Debus Monday, February 19, 2018 3:21 PM
    Monday, February 19, 2018 9:30 AM
  • Hi Patrick,

    pls. see my last post from yesterday (written by my alter ego) to Lydia triggered by your statement. Have a look at it. Some feedback whether this works for you or not would be nice and helpful.

    It'S still a bug. One of those 2.000.000 at living undiscovered only in Windows OS and middleware.


    Regards Jörg

    Monday, February 19, 2018 3:34 PM
  • Hi Jörg,

    Indeed I also use a lot of comments in my SP's.

    But I can't fix the issue by adding an additional comment after the first executable statement. Or what do you understand by the "first executable statement" in a SP? Do you have a sample?

    Patrick

    Monday, March 5, 2018 12:30 PM
  • The problem seems to occur if comments are in a procedure or function SOURCE before the first executable statement, which is normally the CREATE statement. So I place my first comment after the AS statement which terminates the CREATE text and starts the procedure text. Now the yellow arrow points to the correct statement.

    Hope this works for you, too.


    Regards Jörg

    Monday, March 5, 2018 4:50 PM
  • I finally was able to fix this debugging/alignment issue by removing the horizontal white space.  Now the yellow line aligns perfectly instead of being off many lines. So happy!  To do this in SSMS, first highlight your entire stored procedure (ctrl-A) and then from the toolbar select Edit/Advanced/Delete Horizontal White Space
    Monday, March 5, 2018 8:38 PM
  • @Jörg: adding or removing "special" comments won't help me - I did further tests without success. The yellow instruction pointer is correct when running through the SP, but suddenly when jumping over another comment, it starts being misplaced:

    @Brenda: Deleting horizontal white space works, but the code becomes very very ugly!

    BTW. I also always use this Setting under Tools - Options - Text Editor - All Languages - Tabs => Insert spaces

    @Lydia: so it's up to Microsoft to correct this annoying bug, so please vote up my idea:
    https://feedback.azure.com/forums/908035-sql-server/suggestions/32898118-debugging-stored-procedure-in-sql-management-studi

    Or if you have a direct connection to them (Development department), so please emphasize this subject to them.

    Regards,

    Patrick

    Tuesday, March 6, 2018 8:23 AM
  • Hi Patrick, I could not reproduce this misplacing after every additional comment here.

    If you would post a little testcase where it happens, I could try to reproduce it here. Maybe we can find some reaoning if it would work well here.


    Regards Jörg

    Friday, March 9, 2018 5:51 PM
  • BTW. Microsoft changed the state of my connect-idea from "Under Review" to "Unplanned" - no luck ....

    Patrick

    Tuesday, May 8, 2018 9:55 AM
  • It's the same old story: Ship the sh.. and fix it later. They call it "feature update" now: New flavor of this old story: Ship the sh.. and fix it later 2.0 where later is interpreted as "never".

    Regards Jörg

    Tuesday, May 8, 2018 12:28 PM
  • Hi All
    I just stumbled across this thread as I just ran into the problem. 
    I thought it was odd as I know I have occasionally had this in the past, but most times it is OK.
    Then I realised that the stored procedure code in the debugger was an *old* version of what I thought I was debugging.
    Seems you have to hit Ctrl-Shift-R to refresh SSMS so that it gives you the up to date version, and then the debug prompt is fixed.

    Update.  You have to clear all previous breakpoints and *then* press Ctrl-Shift-R.
    If there are any old breakpoints hanging around, the code doesn't get updated in the debugger.

    I'm using v17.9.1, but this probably applies to older versions too.

    Hope this helps someone.



    Saturday, June 15, 2019 1:10 AM