Unable to debug a sproc
-
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?
-
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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.
-
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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
- 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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Apparently, I was unaware there was such a version of SSMS that can debug a sproc... which version is that?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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.
-
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks but my OP was asking abut the debugging feature in VS and if anyone had run into the same errors
- 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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
For those that aren't aware, debugging T-SQL from VS is possible, as per this ridiculous example I've just thrown together.
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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_____________
Code for TallyGenerator -
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.
-
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
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply