Share via

How to: Enable Transact-SQL Debugging

  • Article

This topic applies to:

Visual Studio Ultimate

Visual Studio Premium

Visual Studio Professional

Visual Studio Express
s0fk6z6e.DoesApplybmp(en-us,VS.100).gif s0fk6z6e.DoesApplybmp(en-us,VS.100).gif s0fk6z6e.DoesApplybmp(en-us,VS.100).gif s0fk6z6e.DoesNotApplybmp(en-us,VS.100).gif

Transact-SQL debugging with Visual Studio requires a SQL Server database server, beginning with SQL Server 2005, and setting up the appropriate SQL Server permissions. If the corresponding instance of SQL Server is running on another computer, you'll also need to configure Windows Firewall on both computers.

Note   Debugging Transact-SQL with SQL Server 2000 and earlier is not supported.

You can also use Visual Studio to debug SQL CLR code (database objects that use managed code). To debug SQL CLR on a remote database server, you must install the Remote Debugging Monitor on the computer running the instance of SQL Server. For more information, see How to: Set Up Remote Debugging and SQL CLR Database Debugging.

For more information about debugging Transact-SQL with SQL Server Management Studio, see Using the Transact-SQL Debugger.

To prepare SQL Server permissions on the instance of SQL Server

  1. Add the application account to the SQL Serversysadmin fixed server role. The application account is the Windows account that Visual Studio is running under. To do this, execute the following Transact-SQL statement with a login that is also a member of sysadmin, where Domain\Name represents the Windows account login: sp_addsrvrolemember 'Domain\Name', 'sysadmin'

  2. Add the connection account to the SQL Serversysadmin fixed server role. The connection account is the Windows login or SQL Server login that the Transact-SQL code uses to connect to the instance of SQL Server. To do this, execute the following Transact-SQL statement with a login that is also a member of sysadmin, where Domain\Name represents the corresponding account: sp_addsrvrolemember 'Domain\Name', 'sysadmin'

Note

We recommend that Transact-SQL code be debugged on a test server, not a production server, for the following reasons: Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server. Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session is ended or the transaction committed or rolled back.

To prepare Windows Firewall for remote debugging (when SQL Server is running on a different computer)

  1. On the computer running Visual Studio you must add the following to the Windows Firewall exception list:

    1. The Visual Studio program, Devenv.exe.

    2. TCP port 135

  2. On the computer running the instance of SQL Server, you must add the following to the Windows Firewall exception list:

    1. The SQL Server program, sqlservr.exe. By default, sqlservr.exe is installed in C:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Binn, where InstanceName is MSSQLSERVER for the default instance, and the instance name for any named instance.

    2. TCP port 135

    3. If your domain policy requires network communication to be done through IPSec, you must also open the UDP 4500 and UDP 500 ports.

See Also

Reference

Transact-SQL Debugging Setup Errors and Troubleshooting

Concepts

Debugging Transact-SQL

Other Resources

Setting Up Transact-SQL Debugging

Debug Settings and Preparation

How to: Enable SQL Debugging on SQL 2000 Desktop Engines

How to: Set Up Remote Debugging

SQL CLR Database Debugging