Unable to debug a sproc

  • Jay@Work

    SSCrazy

    Points: 2458

    I'm trying to debug an SP in visual studio 2019 (what a palava! bring back this ability in SSMS please!!)

    Anyways. I have created a connection to the DB and tested successfully. Now I add a script to the project and this contains my exec SPname <parameters> code. Next I select execute with debugger and it highlights my exec line of code.

    I hit F11 to step through and get the message "invalid connection string" I have rechecked the connection many times.

    If I execute the line without debugging then the SP runs fine.

    I'm stuck and mycolleagues haven't encountered this either.

    Any ideas?

  • sgmunson

    SSC Guru

    Points: 110548

    I'd have to wonder how VS would perform this task.   After all, it would likely need the kind of permissions you need in SSMS to be able to see the execution plan or to do a trace, in order to be able to see the stuff going on under the hood.   If you already have those kinds of permission, then you don't really need VS to find out what's going on...  just run SQL Server Profiler while SSMS does the execution.   It might be more complex, but you'll learn a lot more that way...

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Jay@Work

    SSCrazy

    Points: 2458

    With VS running as admin and with your credentials passed with the connection string it does it no problem (at least should).

    Profiler won't help me as the SP runs fine but I do not get the expected output so therefore I need to step through the SP to follow the logic.

  • sgmunson

    SSC Guru

    Points: 110548

    I can't really help with what VS does or doesn't do...   but frankly, why not learn how to debug your sproc the way it's been done for many years...   Look at each query and copy/paste that query into an SSMS tab, and then run it, having declared any needed variables and provided a test value for same.   You might learn something...    It's not necessarily as much work as you might think...

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Jay@Work

    SSCrazy

    Points: 2458

    Thanks I have 20+ years experience. The individual bits run fine.

    I have 67 rows go into a cursor (yeah yeah I know) and it only creates 31 rows. Everytime!

    The stored procedure is over 250 lines and I can't tell where the logic is failing hence the need for a debug environment.

    I have logging code throughout but this isn't unearthing anything strange

  • Jeffrey Williams

    SSC Guru

    Points: 88651

    Jay@Work wrote:

    Thanks I have 20+ years experience. The individual bits run fine.

    I have 67 rows go into a cursor (yeah yeah I know) and it only creates 31 rows. Everytime!

    The stored procedure is over 250 lines and I can't tell where the logic is failing hence the need for a debug environment.

    I have logging code throughout but this isn't unearthing anything strange

    What happens if you just execute the procedure with the same parameters as supplied from VS?  If the problem is in the stored procedure then I cannot see how debugging from VS is going to help.

    There is obviously a problem with the procedure - and it seems you have already isolated the problem to a specific cursor/loop in the procedure.  But you then state you cannot identify where the logic is failing...so how can you be sure the cursor is the problem?

    Either way - if you really need to be able to debug then why not install a version of SSMS that includes that ability?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • sgmunson

    SSC Guru

    Points: 110548

    Apparently, I was unaware there was such a version of SSMS that can debug a sproc...  which version is that?

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • x

    SSC-Insane

    Points: 23593

    So you're saying that you are getting less rows in that routine than you expect? You could query separately for a row that should show up but doesn't and then examine the criteria that should have selected it.

    I'm with Mr. Munson on this, you should try harder without a debugger to understand what the programming code is doing.

     

     

  • Jeffrey Williams

    SSC Guru

    Points: 88651

    https://docs.microsoft.com/en-us/sql/ssms/scripting/transact-sql-debugger?view=sql-server-ver15

    From this document: This feature works with SSMS version 17.9.1 and earlier.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jay@Work

    SSCrazy

    Points: 2458

    Thanks but my OP was asking abut the debugging feature in VS and if anyone had run into the same errors

  • Jeffrey Williams

    SSC Guru

    Points: 88651

    Jay@Work wrote:

    Thanks but my OP was asking abut the debugging feature in VS and if anyone had run into the same errors

    Since Microsoft has removed the capability to debug stored procedures in later versions of SSMS, which is now built on the same platform as Visual Studio - I would not expect later versions of VS to be able to debug stored procedures either.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Phil Parkin

    SSC Guru

    Points: 244732

    For those that aren't aware, debugging T-SQL from VS is possible, as per this ridiculous example I've just thrown together.

    VS SQL Debug

    As you can see, you can step through the code and see the results and locals windows as you step through.

    I have only tried this locally, however.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Sergiy

    SSC Guru

    Points: 109836

    Use RAISERROR with severity 0 (not actually raising any error, just printing a message) to display the state of affairs inside the procedure at any stage of execution.

    That's how it goes:

    DECLARE @Var1 varchar(50), @Var2 int, @RC int 
    SELECT @Var1 = APP_NAME()
    SELECT @RC = @@ROWCOUNT
    IF APP_NAME() LIKE '% Query%'
    RAISERROR ('Query 1 within Cursor, Rows Affected: %d, Current VAR values: @Var1="%s", @Var2=%d ', 0,1, @RC, @Var1, @Var2) WITH NOWAIT

    • This reply was modified 1 month, 1 week ago by  Sergiy.
    • This reply was modified 1 month, 1 week ago by  Sergiy.
  • Jay@Work

    SSCrazy

    Points: 2458

    I'm not seeing errors and have logging action in all the catch and try blocks but only errors that occur at the top level are being logged.

    I'm guessing that those not happening are falling foul of a trigger deeper down and the error is not bubbling back. Will test this theory by putting the data back to how it was and manually trying one row that never made it.

  • Sergiy

    SSC Guru

    Points: 109836

    It's not about catching errors, it's about displaying the status of the process in the message tab without disrupting the execution.

    After the execution is completed you may scroll through the messages and see where did it go an unexpected way.

    I edited the previous post by adding a code sample

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply