Debugging Sql Server 2005 Stored Procedure in Sql Server Management Studio--SSMS
-
I am trying to figure out how to debug a Stored Procedure (SP) from Sql Server Management Studio. Like in Query Analyzer in Sql Server 2000, I could right click an SP and then click debug on it will let me debug the SP. Of course, I would execute sp_sdidebug 'legacy_on' before doing that.
In Sql Server Management Studio I do not see an option for debugging an SP when I right click the SP nor do I see debugging SP option in the menu. I read BOL, and it mentions to run sp_sdidebug. I tried to run sp_sdidebug but I get "Could not find stored procedure sp_sdidebug". I tried this step in master DB, user DB, and msdb but all give me the same error.
The user who is trying to debug the SP is already a member of sysadmin role.
I can debug the SP from Visual Studio 2005, but I would like the same capability I had in Sql 2000 to be in SMSS. Besides, the Results windows in SMSS gives me resultset, which I cannot get from the Visual Studio, if I use VS for debugging.
I have also read that many of the features, such as debugging, xp_cmdshell etc. come disabled by default in SQL 2005, which is okay. But could somebody tell me how to make the entire Stored Procedure debugging process work from within SSMS?
Reply appreciated.
Thanks
-
This was removed by the editor as SPAM
-
If you are updating SQL2000 to SQL2005, save a copy of the following directory structure: C:\Program Files\Microsoft SQL Server\80\Tools\Binn.
If you do not have a saved copy on the system use a system that has SQL2000 installed. Copy the following directory to the SQL2005 installed system:
C:\Program Files\Microsoft SQL Server\80\Tools\Binn
The directory should exist on fresh SQL2005 as well as upgrade SQL2005 installations. I recommend answering No to not overwrite existing files in the \Binn and \Resources directory.
Now you can launch and use the SQL 2000 tools like Query Analyzer; isqlw.exe. I recreated the shortcuts to my favorite SQL2000 tools to be used with SQL2005.
[font="Arial"]Clifton G. Collins III[/font]
-
Even after following my own instructions, I cannot get debugging to work on a simple tsql procedure.
This is a MAJOR flaw with SQL2005. Who wants to install something on a QA/staging server that is not going to be on a production server, to troubleshoot? There are many times that the QA staff will step through code with Query analyzer to assist in the resolution to the issue. They will not be able to do so now, since we want the QA/staging environment the same as production. And we are not about to install the IDE on production.
So the list of useful missing items is increased by one:
· Taskpad
· Ability to quickly script permissions on stored procedures
· Ability to quickly see disk space in database properties
· Time of creation of stored procedures
· Debug TSQL stored procedures
[font="Arial"]Clifton G. Collins III[/font]
-
Sadly, this seems to be true. I cannot find any reference, in BOL, forums, or the SQL Server Management Studio to doing T-SQL stored procedure debugging. That's a really bad decision - now I have to try and get VSS to open my SQL Solution to debug it? My VSS seems to know about Integreation Services, Analysis Services and Reporting Services, but not SQL. Do I need to install a component to get VSS to open SQL projects and debug them? AAAARGH
-
Hi,
I'm just experiencing the same problem: no database project in VSS
Did you find a solution? What do I have to do?
thanx in advance
-
Alas refer here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124550
... "We will implement this feature for the Katmai release of SQL Server."
Cheers,
-Matthew Hobbs
-
With Microsoft SQL Server 2000 it was possible to debug stored procedures from directly within Query With SQL Server 2005, however, this functionality was moved out of SQL Server Management Studio and into the Visual Studio IDE. Using this technique, it is possible to step into your stored procedures, one statement at a time, from within Visual Studio. It is also possible to set breakpoints within your stored procedures' statements and have these breakpoints hit when debugging your application.
1.Open Visual studio
2.Click “view” and then “server explorer”
3.Right click “Data connections” and add connection
4.Expand Stored Procedure
5.Select Procedure which need to debud
6.Right click and choose option “Step Into Stored Procedure”
For details, pl refer: http://allaboutoraclemssql.blogspot.com/2008/05/tsql-sql-server-2005-debugging.html
-
SQL Server 2008 Management Improvements – T-SQL Debugger
I’m here at the SQL Connections conference in April of 2008 and I’m giving a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post I’ll talk about the Transact-SQL Debugger tool.
Those of you who have been using SQL Server for many years are probably already familiar with this improvement. In fact, you’re one of the reasons it is included in SQL Server 2008. See – we do listen!
A “Debugger” is simply a tool that helps you move through your code, line by line, showing you the current state of the variables in the program, an output window where you can observe what the code is doing, error windows, and the ability to navigate backward and forward through each line. The T-SQL debugger we’ve created does just that, and more.
You can set “breakpoints”, which means that you can place a mark by a certain line of code, and then tell the debugger to run the code up to that point and then stop.
Even if you’re not a hard-core T-SQL developer, you probably do write code from time to time. The De bugger is
included right on the main menu line, so you don’t have to launch anything special to run it. Just type some code (even a system stored procedure like sp_adduser will do) and select the Debugger menu item. The system will show a few more windows, but won’t appear to be doing anything – just click the icon in the icon bar that has the tip-text of “Step into” and watch the screen. Play around with the Step Over features, left-click next to a line of code to set a breakpoint, and generally experiment with the feature. It’s a great way to fix problems, and it’s even a useful way to learn!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply