step into stored procedure from c# visual studio (community, 2019) project--nothing suggested over last 10 years works

Question

I'm not a novice--been working with VS since it hit the market (every version). Worked for more than 20 years as SQL Server DBA, also. However, after 30+ hours of Bing research and trying EVERYTHING even hinted, I can't get it to work--error: won't hit the [SQL] breakpoint. Database (SQL Server 2019) is built as separate VS solution (not the test solution) sqlproj. Only possible distinguishing factor may be that the VS project calling the stored proc is a Service Fabric service running in a local cluster (1-node). Database is on local machine (not Azure). Other interesting notes: breakpoint shows (empty circle) in breakpoint window (before and while debugging fabric service c# project) whether store proc project file tab window is open, or not--meaning the breakpoint is defined in the "other", sqlproj solution project. [Of course I tried ignoring the sqlproj, starting out--as often mentioned in research hints.] I really thought that listing the "other" database project as a [debug project] reference would "make a difference"--but the referenced [database project] assembly does not show up in modules windows list (for forcing the pdb load). I also checked the GAC and database project assembly is not there. The pdb load was modified to include the database project and the pdb and dll both are included in the debug project [fabric service] debug bin folder. The most frustrating thing (other than the paucity of documentation on this issue) is that I was unable to derive even an educated guess as to how VS debug takes the simple opening of a stored procedure (from VS sql server object explorer), and setting a breakpoint (or having one setup in a separate sqlproj) was binding upon the c# debug activity so that the F11 step into would know about it. That left me to all kinds of debug option settings and other "black art" type attempts, rather that rationally following the trail of steps that leads to exactly where the breakdown in this debug capability occurs.

Answer 1

Hi jhg-5209,

Actually, since VS2017, VS supports to debug the stored produce directly in VS.

If I understand this issue correctly, you can create a sq database project directly in VS2019 and then follow the steps below:

1) first connect to your current database under View--> SQL Server Object Explorer and make sure that VS successfully established a connection with your database.

2) right-click on the sql database project--->right-click on the project Properties-->set Target Platform to SQL2019. After that, create a new item called Stored Proceduce and put your stored proceduce into that file.

Note that the stored produce cannot set a breakpoint and you should use exec command to invoke that procedure and set a breakpoint there.

Add the command under the sql file:

Exec [dbo].[xxx](the procedure)

3) When you finish it, first click Build to build the project to generate the pdb file and then click SQL top menu-->Execute with Debugger to debug the procedure.

You can also refer to this document about how to: Debug Stored Procedures in VS.

Besides,

this is about how to debug a database project directly in VS and it is officially recommended and if you used other ways and had other distinctive details, please let us know. If I misunderstand your issue, please let us how you wish to debug such procedure in VS and give us any detailed info to help us understand your issue.

Best Regards,
Dylan

Answer 2

So, if I understand your answer: VS (starting with v. 2017) does not "step into" stored procs from the c# debugging activity.

Please verify that what I understand (step into is not possible from VS) is correct. The f11 activity can not be done from c# debugging.

jhg