Is there a way to step into the stored procedure code in SQL Server Management Studio? I know this is possible with Visual Studio, but I am looking for a dependable debugging solution from within Management Studio
-
2Not that I'm aware of in SSMS 2005 - you're left to using PRINT statements & conditional logic/flow controlOMG Ponies– OMG Ponies2010-01-06 22:49:33 +00:00Commented Jan 6, 2010 at 22:49
-
Did you ever get a solution to this? None of the answers below are valid.user3335999– user33359992021-09-30 22:39:30 +00:00Commented Sep 30, 2021 at 22:39
Add a comment
|
6 Answers
Only with SQL 2008 and SSMS 2008. Select from menu 'Debug\Start Debugging' or press Alt+F5 and will launch the step by step T-SQL debugger.
On 2005 the only way is to attach Profiler and monitor for the SP:StmtCompleted event, which is not exactly debugger step-by-step, but at least you'll see the execution flow. Not to be done on a production machine, obviously.
9 Comments
Nick
I get the error "Failed to start debugger" The EXECUTE permission was denied on the object 'sp_enable_sql_debug' .. on SSMS 2008
Remus Rusanu
>> To debug on SQL Server 2005, both the account running Visual Studio and the account used to connect to SQL Server must be members of the sysadmin role. msdn.microsoft.com/en-us/library/ms241735(VS.80).aspx
HLGEM
And it never occurred to them that people who aren't sys admins might need to debug sps?
Remus Rusanu
@HLGEM: debugging is somethign you do on the dev box, not on production. Besides, there are very stong reasons for this: anyone with debug priviledges is a de facto admin of the box, simply by means of the power invested in what debuggers can do. So requiring you to be sysadmin is simply aknwoledging a fact and closing a possible priviledge escalation path.
levininja
@RemusRusanu In some companies, software developers write stored procs even though they're not the dba who is the only one with sysadmin priviledges. Doesn't it make sense to be able to debug your own sp if you're in that role?
|
I have written a pretty detailed blog post about it here:
Basically the gist of it is that you enter your 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.
This is very handy but no one seems to use it.
1 Comment
user3335999
Down vote. Incorrect. Pressing the execute button will not start a debugging session, it will only execute the sql.
From SSMS 17 onwards version, You can not debug any SQL.
[Edit]
Use Visual Studio with SSDT tool. You can allow to debug.
3 Comments
user3335999
This is the correct answer. M$ has removed all ability to debug SQL with SSMS.
Jatin Dave
Check this out, I haven't checked. learn.microsoft.com/en-us/answers/questions/191819/…
ramdev
how do you debug SQL Stored Proc now?
The ability to debug sprocs, which was in Query Analyzer in SQL Server 2000 was not put into SQL Server 2005. Microsoft realized this mistake and put the functionality back in in SQL Server 2008.
In SSMS 2008 you can start the debugger by either clicking the debug button on the toolbar or pressing ALT+F5.
Unfortunately, running SSMS 2008 against a 2005 database will not allow you to debug, so you'll have to stick with Visual Studio.
Comments
Watch out for debugging stored procedures using Visual Studio. The way that this is implemented is to use an incredible amount of locking so that you should never do this on a production system.
Comments
Yes, in SSMS 2008 you can definitely step into T-SQL code (code block, stored func, stored proc) and debug it.
See this excellent article Management Studio Improvements in SQL Server 2008 for details - it also deals with debugging (towards the end of the article).
