How to: Enable Transact-SQL Debugging
This topic applies to:
Visual Studio Ultimate
Visual Studio Premium
Visual Studio Professional
Visual Studio Express
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
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'
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'
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)
On the computer running Visual Studio you must add the following to the Windows Firewall exception list:
The Visual Studio program, Devenv.exe.
TCP port 135
On the computer running the instance of SQL Server, you must add the following to the Windows Firewall exception list:
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.
TCP port 135
If your domain policy requires network communication to be done through IPSec, you must also open the UDP 4500 and UDP 500 ports.