Stored Procedure Debugging

SQL SERVER2008 Stored Procedure debugging

Yesterday was asked how the stored procedures in SQL Server debugging, I used to write stored procedures, debugging method is very clumsy, is the execution of statements, and then see the results are not expected, this debugging method, it is a helpless move, greatly restricting the development of speed and accuracy. Today, in his point of reference, I studied the debugging of stored procedures under SQL SERVER 2008.

1. Debugging stored procedures in SQL SERVER2008

The following is a quote from MSDN

SQL SERVER 2008 is ssms to support Single-step debug, but permissions must be configured before debugging.

If SQL Server Management Studio runs on the same machine as the SQL Server database Engine instance, there is no configuration requirement for running the Transact-SQL debugger. However, to run the Transact-SQL debugger when SQL Server Management Studio is running on a different computer with an instance of the database engine, you must use the Windows Firewall Control Panel application on both computers to enable the program and port exceptions.

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

Add TCP port 135 to the Exceptions list.

Adds a program sqlservr.exe to the Exceptions list. By default, Sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\mssql10. Instance name \Mssql\Binn, where the instance name is MSSQLSERVER for the default instance, The name of the corresponding instance for any specified instance.

If domain policy requires network traffic over IPSEC, you must also add UDP port 4500 and UDP port 500 to the exception list.

On the computer running SQL Server Management Studio, specify the following information in Windows firewall:

Add TCP port 135 to the Exceptions list.

Add the 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 are as follows:

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

The Database Engine Query Editor window must be connected by using Windows authentication, or by using a SQL Server Authentication logon name that is a member of the sysadmin fixed server role.

The Database Engine Query Editor window must be connected to an instance of the SQL Server 2008 database engine. If the Query Editor window is connected to an instance in Single-user mode, you will not be able to run the debugger.

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

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

When you investigate the run of multiple Transact-SQL statements, the debugging session usually runs for a long time. A lock acquired by a session, such as an update lock, may hold for a long time until the session is terminated or the transaction is committed or rolled back.

Start the Transact-SQL debugger to place the Query Editor window in debug mode. When the Query Editor window enters debug mode, the debugger pauses at the first line of code. You can then step through the code, pause execution on a specific Transact-SQL statement, and use the debugger window to view the current execution state. You can start the debugger by clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu.

The Query Editor window remains in debug mode until the last statement in the Query Editor window finishes or you stop debugging mode. You can use any 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 Debugging button.

On the Query menu, click Cancel Execution of query.

On the Query toolbar, click the Cancel Execute Query button.

You can also click Detach All on the Debug menu to stop debug mode, but allow the remaining Transact-SQL statements to complete execution.

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

2. Debug stored procedures in Visual Studio (for example, VS2010)

Open Server Explorer, add a SQL Server 2008 database connection, and then right-click on the Stored Procedure node and select "Step into a stored procedure" to step into the Single-step state, along with the top procedure, and set permissions.

using vs to debug stored procedures

We must write or analyze a complex error-making process, I would like to be able to debug VB code like debugging, but the SQL Server itself does not provide a debugger, we can use vs for debugging.

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13-14

Create PROCEDURE testprocdebugger @id int as BEGIN declare @testVal1 int set @testVal1 = @id Declare @em PID varchar DECLARE @shipperid varchar SELECT @empid =shipperid, @shipperid =empid from orders where Orderi d= @testVal1 Select @empid, @shipperid end

Step one: Start Visual Studio, point "try"-> "Service Explorer"

Step Two: Add a data connection.   Fill in the database name, login account, and the database to connect, click OK.        Step three: Right click on the debug process, select Step into Stored procedure step fourth: The debugger will prompt you to enter several values for the number of bars, and then click OK to enter the debug state, depending on the parameters of the stored procedure. Now you can debug like VB Code, step-by-step tracking.

Step Two: Add a data connection.

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

Step three: Right click on the error procedure to debug, select Step Into Stored procedure

Fourth step: The debugger will then according to the stored procedure parameters, prompts you to enter several values for the number of bars, and then click OK to enter the debugging state.

Now you can debug like VB Code, step-by-step tracking.

Category: Development skills