Hãy nâng cấp lên Microsoft Edge để tận dụng các tính năng mới nhất, bản cập nhật bảo mật và hỗ trợ kỹ thuật. Show Transact-SQL Debugger - Call Stack Window
Trong bài viết nàyApplies to: The Call Stack window displays the modules on the call stack, and the data types and values of any parameters that are passed to the modules. Transact-SQL modules include stored procedures, functions, and triggers. To display the call stack, you must be in debug mode. This feature works with SSMS version 17.9.1 and earlier. Task ListTo access the Call Stack window
To change the current Call Stack frame You can use either of the following procedures to make a stack frame the current frame:
To view the source of a frame other than the current frame
Stack FramesEach row in the Call Stack window is called a stack frame and represents either a call from a Transact-SQL script file to a module or a call from one module to another. The bottom stack frame in the display indicates the line in the Database Engine Query Editor window that made the first call into the stack. The top row indicates the line on which the debugger paused execution, and is identified by a yellow arrow in the left margin of the window. Each intermediate row indicates the module and the line number of the source code that called the next higher stack frame. All expressions in the Locals, Watch, and QuickWatch windows are evaluated based on the current stack frame. The Query Editor window displays the code for the current frame. By default, the current stack frame is the frame in which the Transact-SQL debugger paused execution. When you change the current stack frame to another frame, the expressions in the Locals, Watch, and QuickWatch windows are reevaluated in the context of the new frame, and the source code of the new frame is displayed in the Query Editor window. ColumnsName Displays information about a module on the call stack. For the bottom row in the call stack, Name lists the Query Editor source window and line number of the first call into the stack. For the other rows, Name has the format Module(Instance.Database)(ParmList) LineNumber. If you recall your days working with SQL Server 2000, you would remember debugging a routine (Stored Procedure, UDF and trigger) in Query Analyzer, as a debugger tool was available with it. Starting with SQL Server 2005, Query Analyzer and Enterprise Manager had been clubbed together as SQL Server Management Studio (SSMS). Though this single interface has simplified working with SQL Server, one major drawback was, it does not allow you to debug a routine from there. For that purpose you needed Visual Studio (Enterprise and Professional) edition installed, on your development machine, which allowed you to debug a routine. The requirement to install Visual Studio is something that database developers and DBAs would be reluctant to do as it requires additional funds for a Visual Studio license and puts additional pressure on the physical box after installation. SolutionDebugging capability in SSMS was a long sought feature by users ( http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124550 , http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124613 ) and finally the Microsoft SQL Server team decided to provide this feature in SQL Server 2008. With this feature you can navigate through your code, line by line, watch the current state of variables in the program, an output window where you can observe what the code is doing, error windows, the ability to navigate backward and forward through each line, observe system objects such as the call stack and threads, not only this you can even place "breakpoints" and then tell the debugger to run the code up to that point and then stop. Example You can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu or pressing ALT+F5. In the image below I am executing a batch in debug mode; you can notice several windows on the bottom though details of Locals window are only visible which displays information about the local variables in the current scope of the T-SQL debugger.  Let's see another example.� The script for this is provided in the below table, a procedure calls another procedure, so during execution if you look at the Call Stack window you will get the current execution location, and information about how execution passed from the original editor window through any T-SQL routines (functions, stored procedures, or triggers) to reach the current execution location as shown in the image below. � Script 1: Create a procedure which calls another procedureCREATE PROCEDURE PROC1 AS BEGIN PRINT 'A' PRINT 'B' EXECUTE PROC2 END GO CREATE PROCEDURE PROC2 AS BEGIN PRINT 'C' PRINT 'D' END GO EXECUTE PROC1 GO Note SQL Server Management Studio must be running under a Windows/SQL Server account that is a member of the sysadmin fixed server role or else you will get an error something like this. If you are running the T-SQL debugger when SQL Server Management Studio is running on a different computer from the instance of the Database Engine, you must enable program and port exceptions by using the Windows Firewall Control Panel application on both computers, for more details click here. Microsoft recommends that T-SQL code should be debugged on a development/test server and not on a production server because first it requires the member to be part of sysadmin fixed server role to debug and second it may hold resources for longer period while you debug and investigate. Limitation If you are connecting to SQL Server 2005 even from SQL Server 2008 SSMS, you would not be able to debug your routines and get this error, it happens because T-SQL debugger includes both server-side and client-side components. So in a nutshell, it works with SQL Server 2008 only so far. The T-SQL debugger does not support the Microsoft Visual Studio features of setting breakpoint conditions or hit counts. Next Steps
About the authorView all my tips Comments For This ArticleWednesday, August 15, 2012 - 4:30:18 PM - Shekinah (19054) you should try test drvien development you'll code faster by writing unit tests first then writing the code to make the tests pass it's hard getting used to at the beginning but it'll save u hours of debugging later.if you do TDD properly you might never need to use a debugger Friday, August 5, 2011 - 8:28:44 AM - Rick (14317) This is fairly useless for your run-of-the-mill SQL developer since nobody is going to hand out the sysadmin server role... Microsoft needs to make this so it will function without sysadmin. If the concern is that this allows someone to step into what should be a protected system SP or Function then implement protection in the debugger to prevent that rather than requiring the sysadmin role. Thursday, February 26, 2009 - 11:42:40 AM - jerryhung (2826) I must say, while SSMS 2008 has many cool GUI features - IntelliSense, Debugger They are useless if they ONLY work with SQL Server 2008, which will probably take 10 years for everybody to be on it |
