Stored Procedure Debugging

SQL SERVER2008 Stored Procedure debugging

Yesterday was asked how the stored procedures in SQL Server debugging, I have written a stored procedure, debugging method is very clumsy, is to run the statement, and then see the results are not expected, such a debugging method, is really helpless, greatly restricting the development speed and accuracy. Today, under his point of reference, the debugging of stored procedures under SQL SERVER 2008 is studied.

1. Debugging stored procedures in SQL SERVER2008

The following is a quote from MSDN

SQL SERVER 2008 's SSMS supports single-step debug, but permissions must be configured before debugging.

Assuming that SQL Server Management Studio is executing on the same computer as an instance of the SQL Server database engine, there is no configuration requirement for the Execute Transact-SQL debugger. However, when SQL Server Management Studio and the database Engine instance are executing on different computers, to execute the Transact-SQL debugger, you must use Windows firewall on both computers Control Panel application to enable program and port exceptions.

On the computer that executes the instance of the database engine, specify the following information in Windows firewall :

• Add TCP port 135 to the Exceptions list.

• Add the program Sqlservr.exe to the Exceptions list. By default, Sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\mssql10. Under instance name \Mssql\Binn, the instance name is MSSQLSERVER for the default instance and the name of the corresponding instance for whatever instance you specify.

• Assuming that the domain policy requires network communication over IPSEC, UDP port 4500 and UDP port 500 must also be added to the exception list.

On the computer that is executing SQL Server Management Studio, specify the following information in Windows firewall :

• Add TCP port 135 to the Exceptions list.

• Add program Ssms.exe (SQL Server Management Studio) to the Exceptions list. By default, Ssms.exe is installed under C:\Program Files\Microsoft SQL Server\100\tools\binn\vsshell\common7\ide.

• The requirements for starting the Transact-SQL debugger include the following:

  • SQL Server Management Studio must be executed under a Windows account that is a member of the sysadmin fixed Server role.

  • The Database Engine Query Editor form must be connected by using Windows authentication or by using a SQL Server authentication login that is a member of the sysadmin fixed Server role.

  • The Database Engine Query Editor form must be connected to an instance of the SQL Server 2008 database engine. Assuming that the Query Editor form is connected to an instance in single-user mode, you will not be able to execute the debugger.

  We recommend that you debug Transact-SQL code on a test server instead of debugging on a production server for the following reasons:

  • Debugging is an operation that requires high privileges. Therefore, only the members of the sysadmin fixed Server role are agreed to debug in SQL Server.

  • When you investigate the execution of multiple Transact-SQL statements, the debugging session typically takes a very long time. A lock that a session acquires, such as an update lock, may hold for a very long time until the session is terminated or the transaction is committed or rolled back.

  Start the Transact-SQL debugger to put the Query Editor form into debug mode. When the Query Editor form enters debug mode, the debugger pauses at the first line of code. You can then step through the code, pause the run on a specific Transact-SQL statement, and use the debugger form to view the current running state. You can start the debugger by clicking the Debugbutton on the Query toolbar, or by clicking Start Debugging on the debug menu.

  The Query Editor form remains in debug mode until the last statement in the Query Editor form finishes or you stop debugging mode. The ability to use either of the following methods to stop debug mode and statement execution:

  • On The Debug menu, click Stop Debugging.

  • On The Debug toolbar, click the Stop Debuggingbutton.

  • On The Query menu, click Cancel Run Query.

  • On the Query toolbar, click the Cancel Run Querybutton.

  You can also click All Detachon the debug menu to stop debugging mode, but agree to complete the remaining Transact-SQL statements.

Original http://msdn.microsoft.com/zh-cn/library/cc646024 (v=sql.100). aspx

2. Debug stored procedures in Visual Studio (take VS2010 as an example)

Open the Server Explorer, join a SQL SERVER 2008 database connection, and then right-click on the Stored Procedure node and select "Step into the stored procedure" to enter the single-step debugging state, the same upper process, and also set permissions.

Using vs Debugging Stored Procedures

We must be in the process of writing or analyzing a complex error, very much want to be able to do the same as VB code debugging, unfortunately, SQL Server itself does not provide a debugger, we can use VS to Debug.

I wrote a simple stored procedure that describes the steps to debug a stored procedure with vs.

?

1234567891011121314

Create PROCEDURE testProcDebugger    @idintASBEGIN    declare@testVal1 int    set@[email protected]    declare@empid varchar    declare@shipperid varchar     select@empid=shipperid,@shipperid=empid fromorders    where[email protected]     select@empid,@shipperidEND

First step: Start Visual Studio, point to "try", "Service Explorer"

Step two: Join the data connection. Fill in the database name, login account, and the database to connect, click OK.      The third step: Right-click on the procedure to debug, select "Step Into Stored procedure" Step fourth: At this point the debugger will be based on the parameters of the stored procedure, you are prompted to enter several values for the number of bars, and then click OK to enter the debug state. Now we can be like debugging VB code, as a step-by-step tracking.

Step two: Join the data connection.

Fill in the database name, login account, and the database to connect, click OK.

Step Three: Right-click on the procedure to debug and select "Step Into Stored procedure"

Fourth step: At this point the debugger will be based on the parameters of the stored procedure, you are prompted to enter several values for the number of bars, and then click OK to enter the debug state.

Now we can be like debugging VB code, as a step-by-step tracking.

Category: Development skills

Stored Procedure Debugging