I have Visual Studio .Net project which uses ADO connection to call stored procedure of SQL Server database.
It is possible to set a breakpoing at stored procedure and debug it. I have local SQL Server and SQL Enterprise Edition installed.
-
are you talking about debugging the actual stored procedure itself? and is the stored procedure managed code or TSQL?Agent_9191– Agent_91912009-12-08 19:56:51 +00:00Commented Dec 8, 2009 at 19:56
-
2 agent I think it is TSQL. It is stored on DB side ALTER procedure [aaa].[my_Proc] begin ...Captain Comic– Captain Comic2009-12-08 20:02:24 +00:00Commented Dec 8, 2009 at 20:02
Add a comment
|
5 Answers
AFAIK, and if you are talking about an actual t-sql stored procedure, Your best bet will be:
- figure out what parameters you are passing into the stored procedure when (if?) it causes problems
- Extract the actual SQL code from the body of the stored procedure and copy that into a new query analyzer window,
- Run that script with the values from #1.
3 Comments
Captain Comic
the problem is that one SP is called when it shouldn't be. I need to 'catch' who's calling it
peacedog
Ahhh, well yes you can put a breakpoint in your code and do it that way. Do you have an idea of where in the code the call might be coming from? If not, you may need to do a lot of F10 work, but you'll find it eventually. It may help to keep SQL Server Profiler open and running as you go.
Captain Comic
Sure I know this way. But my question was if there is debug mode in SQL server where I can put breakpoint using SSMS and wait until some application triggers it
There are a couple options i have heard of for debugging stored procs- eg: Direct Database Debugging.
here is a link from a good article i found explaining some of these: Stored Proc Debugging Methods
Comments
You can debug CLR procedures and functions from VS, see Debugging SQL
Transact-SQL is a different story, it needs special configuration steps and can be debugged from a special tool, see Using the Transact-SQL Debugger.
Comments
Use the SQL Server Profiler (under Tools menu in SQL Server Management Studio) to see the exact calls being sent from ADO to your database procedures. Then you can figure out where the problem is and start debugging, whether in SSMS or Visual Studio.
Comments
I would strongly suggest that you take a look at the built in tooling in SQL management studio.
i have written a pretty detailed blog post about it here:
basically the gist of it is that you enter you sql query to execute your stored procedure, and instead of pressing F5 or hitting the exclamation, you hit the play button and use F10 and F11 to step through and step into your stored procs.
very very handy - and no one seems to use it.
