|
Introduction
There are many times when using SQL Server 2000 Query Analyzer to debug Sql statements is a better choice than debugging in the .NET Visual Studio environment. I generally choose SQL Query Analyzer to debug SQL if the bug is narrowed down to the SQL stored procedure. If you have narrowed down the issue to SQL, there is no reason to bring in the overhead of an application development environment such as Visual Studio. Finding the problem quickly includes not loading down the machine's processing with unnecessary stuff like the calling application. This assumes that whatever permissions you are using can be faked or bypassed so that the security context is the same as the original buggy circumstances in the calling application.
Server/Client/Network Setup
Depending on what version of the Windows operating system and SQL Server and their service packs, debugging may not work on your first attempt. If you are debugging on the server, you only have one machine to check. If you are debugging on a client, you will need to ensure both client and server are installed and configured correctly. If you are using anything beyond a simple LAN, you may also have problems to check with the network including RPC configuration, Firewall configuration, etc. A good Knowledgebase article to help troubleshoot these issues is: 280101 INF - Transact-SQL Debugger Limitations and Troubleshooting Tips for SQL Server 2000
(http://support.microsoft.com/default.aspx?scid=kb;en-us;280101).
You will not be able to debug through a terminal/remote session until this Microsoft issue is fixed: 280100 BUG - Transact-SQL Debugger Is Not Available Through Terminal Server Session
(http://support.microsoft.com/kb/280100/EN-US/).
Security/Permissions
In order to enable debugging on a local machine, the user account you log on to Query Analyzer with needs to have Execute permission on the extended stored procedure 'sp_sdidebug' found in the master database. Be careful that any other SQL permissions or authentication is still in effect or attempt to debug using a different user account than the actual bug demonstrates.
Wing It
All the above configuring may sound like too much work. If you are debugging via Visual Studio, you have probably done most or all of this work to be able to debug from inside Visual Studio. If you have not done any debug configuration for SQL Server, just try to debug a stored procedure. It may work on the first attempt.
Finding the Debugger
The Query Analyzer for SQL 2000 has a tree view called "Object browser" that allows you to view everything on the SQL server you connected to. If you have not used the object browser yet, you can turn it on from the Tools menu | Object Browser | Show/Hide menu option.
Once you have the object browser tree view visible, select a stored procedure in a database and right-click on the stored procedure name. The right-click menu should have a 'debug' option at the very bottom.
If this menu option is grayed-out, you should review the permissions associated with your current user.
Example 1: Debugging a Simple Stored Procedure
In order to illustrate the debugger features, create the stored procedure listed below.
CREATE procedure DebugA
(
@IntIn int,
@IntOut int OUTPUT
)
as
Set @IntOut = @IntIn + 10
Return
This is a simple stored procedure that takes a number and adds 10 to that number and returns the new number in an output variable. You can execute the stored procedure with the following code in Query Analyzer:
Declare @NewInt int
exec DebugA 5,@NewInt OUTPUT
Select @NewInt
Given a value of 5, adding 10 will return a value of 15.
The Debug Procedure Window
Right click on the stored procedure name in the object browser tree view and choose 'debug'. This will bring up a 'Debug Procedure' window for you to enter initial values for any parameters.
You can enter a value in the 'Value' textbox at the bottom right or you can choose to set the value to null by checking the 'Set to null' check box.
You will need to check 'Set to null' for each of those parameters that are treated as OUTPUT only.
If you do not want the debug session to affect your database, make sure to check the 'Auto roll back' check box. This will roll back any changes to the database made while debugging the stored procedure.
When you are done setting the initial values, click on 'Execute' to enter the debugger. The debugger will stop on the first line of code to execute.
Note: I usually close the object browser while I'm debugging to get extra screen space.
The Debugger Window
The debugger has some tool icons above the main window to allow controlling what part of the code you stop at and examine or enter into. Below these tool icons is the main debug window where you can see your stored procedure code. Below this main code window are two more sections.
The first section has 3 parts to show local variables, global variables and your call stack. The local variables can be changed will in the debugger. The global variables can be added or removed will in the debugger. A couple of global variables I add are the @@rowcount and @@identity variables. You might want to become more familiar with the '@@' variables to see which ones might help you in your debugging efforts.
The last section is the results pane which displays just as the Query Analyzer does when not in debug mode. If you use result sets or 'Print' statements, these will be displayed in the bottom window.
Back to Example 1
If you have followed along so far, the debug window local variable should show the @InInt with a value of 5 and the @OutInt with a value of null. Step over every code line until the 'return' statement. Now the @OutInt value should be 15. The callstack window is meaningless in this example because this is not a nested stored procedure.
Example 2: A Nested Stored Procedure
In this example, will keep the T-SQL code simple but add another stored procedure in order to understand the callstack window. In order to illustrate this, execute this code to create these stored procedures:
CREATE TABLE [Table1] (
[TestId] [int] IDENTITY (1, 1) NOT NULL ,
[A] [int] NOT NULL ,
[B] [int] NOT NULL ,
[Total] [int] NOT NULL
)
ALTER procedure DebugA
(
@IntIn int,
@IntOut int OUTPUT
)
as
Declare @Id int
exec DebugB
@IntOut,
10,
@IntOut OUTPUT,
@Id OUTPUT
return
Create procedure DebugB
(
@IntIn int,
@IntConst int,
@Total int OUTPUT,
@Id int OUTPUT
)
as
Set @Total = @IntIn + @IntConst
Insert into Table1
(A,B,Total)
values
(@IntIn,@IntConst,@Total)
Select @Id = @@identity
Return
This creates a table 'Table1', and changes the 'DebugA' stored procedure to pass the input values to a second stored procedure 'DebugB'. 'DebugB' then inserts a row into the new table and returns the auto-incrementing identity value for that row (Id) and the total of the input variables plus 10. This new total and the Id value are passed back to the first procedure which returns them output variables.
Once you have run the code, right-click on 'DebugA' in the object browser and select 'debug'. Enter the first parameter as 5, the second parameter as null and click on the execute button.
The local variables and global variables windows should show the same numbers from the last example. Click on the 'Step Into' tool on the debug tool bar or press F11.
The code window will show the code for 'DebugB'. The local variables, global variables, and call stack windows will all change to reflect the new state of the debugger. You can select 'DebugA' in the callstack to review the state of the debugger before this call.
In the Globals window for 'DebugB', add the @@rowcount and @@identity variables. They should have a zero value after added.
Now step over the first statement setting the value for @Total. Notice that @Total now has a value of 15 and @@rowcount has a value of 1. Step over the next statement, inserting these values into the table. @@transaction should be incremented to 1, @@rowcount should have a value of 1 (1 row inserted), and @@identity should have a value of the Id column. Id should be 1 if you haven't entered any other rows.
If you had a bug in an insert, update, or delete statement, the fastest way to tell is that @@rowcount is 0 after the statement is run. This is also a good way to ensure your where clause was tight enough to capture only the rows you intended.
Now step over the select statement that sets the @Id field. All the output values are now set and the procedure is effectively completed. You can step over the return statement to return to the 'DebugA' procedure.
'DebugA' should now show the output parameter of @OutInt with a value of 15. The statement indicator should be sitting on the 'return' statement. In order to demonstrate changing a local variables, click on the local variables for @OutInt and change the value to 20. Now click over the 'return' statement. The @OutInt parameter ended with a value of 20 because it was set that way.
Why would you want to change a local variable in debug mode? Suppose your stored procedure has several problems. Instead of fixing one and starting over, change the local variable affected and then continue. You know where the first problem is and you won't loose any time moving on to the second bug.
Complex Code Such as Cursors
Debugging adds power when your code becomes more complicated. Cursors can have so many things going on that debugging is vital. A good example is nested cursors that affect many different tables. In order to get a debugger without using SQL Query Analyzer debugging, you may have been tempted to put this type of complexity into the calling application and use its debugger (such as visual studio), avoiding cursors or nested procedures altogether. SQL Server 2000 debugger has a long way to go but just to have this feature is a leap in the right direction. This puts the T-SQL code logic where it belongs and gives you the power to manage that code.
Example 3: A Cursor
Create a cursor with the following code:
create procedure DebugCursor
as
DECLARE cursorDebug Cursor
FOR
Select
TestId,
A,
B,
Total
From Table1
Open cursorDebug
Declare @cursorTestId int
Declare @cursorA int
Declare @cursorB int
Declare @cursorTotal int
Declare @randomDifference int
Fetch NEXT FROM
cursorDebug
INTO
@cursorTestId,
@cursorA,
@cursorB,
@cursorTotal
While (@@FETCH_STATUS =0)
BEGIN
-- generate random number seeded by row id
Set @randomDifference = Convert(int,Rand(@cursorTestId))
Update Table1 Set
@cursorTotal = @cursorA + (@cursorB * @randomDifference)
Where
TestId = @cursorTestId
END
FETCH NEXT From
cursorDebug
INTO
@cursorTestId,
@cursorA,
@cursorB,
@cursorTotal
CLOSE cursorDebug
DEALLOCATE cursorDebug
GO
This cursor changes the second example in that the total value inserted into the database is alter by a random percentage for each row based on the row Id.
Right-click on 'DebugCursor' in the object browser and click on 'Debug'. There are no input or output parameters, so there is nothing to set. Click on the 'Execute' button.
This brings up the SQL Query Analyzer Debugger and shows the first statement that can be executed.
Add the @@fetch_status to the global variables. @@fetch_status indicates if a row was returned into the cursor variables. This is a good indicator that your select statement to fill the cursor isn't doing what you intended.
Step over the statements until you are at the 'Set' statement in the cursor loop. Notice that the @randomDifference variable is null. Now step over the statement and notice the variable has a value of a percentage (since it's a float).
The next statement is an Update statement that sets the column B the old value of B multiplied by the new random value. This effectively makes every value for B different than every other row and alters the total as well. If you have been following along this article, the table only has one row so the @@rowcount after the update should be 1.
If the cursor calls other stored procedures, you can easily track the code and values into those stored procedures as well.
Summary
SQL Server 2000 Debugging is a huge step forward. You can have complex, nested code in stored procedures and be able to test them confidence regarding the statement of the database at each statement. This allows you to keep the transactional power and processing speed on the SQL server instead of trying to reinvent those features in the calling application.
|
