Use Whidbey to Debug TSQL/CLR Procedures On Yukon Server(By Samuel Zhang)

  • Article

There is a nice feature in Visual Studio 2003 that allows user to debug TSQL on SQL Server. With Whidbey, you can do that with Yukon Server, not only that, you can also debug CLR Managed Procedure/Function/Trigger from Server Explorer!

In current Whidbey build, you have to do some manual configuration to enable that, especially if you have deployed server pack 2 on you Windows XP box

 

How to use Whidbey to debug TSQL for Yukon

 

a) If you are using WinXP SP2, make sure Yukon Service is running under a domain account. If you are not using WinXP Sp2, please skip step b and c.

 

b) If you are using WinXP SP2, run \windir\system32\gpedit.msc. In the Group Policy window, in the Tree tab, under Local Computer Policy, open the Computer Configuration. Open Administrative Templates. Open System. Open Remote Procedure Call Disable Restrictions for Unauthenticated RPC clients. In the Group Policy title bar, click the close box

 

c) If you are using WinXP SP2, run regedit.exe, delete 'HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Windows NT\RPC\RestrictRemoteClients', and reboot this computer.

 

d) Open Whidbey to connect to a Yukon server with either

  1. a SQL account. also make sure the user account (NT login) on the client machine is the administrator account on the Yukon server.
  2. Or a NT domain user(with NT authentication mode). Make sure that domain account has permission to run the stored procure sp_enable_sql_debug on Yukon server

e) In server explorer, right click a TSQL stored procedure to select step into

 

f) If you are using WinXP SP2, you will see a warning dialog. Select the third option in firewall warning dialog. That allows every machine to go through firewall. Now you should be able to do TSQL debugging against Yukon

 

How to use Whidbey to debug CLR Proc/Function for Yukon Server

 

a) Login to the Yukon server with an account which is a local Administrator account on Yukon server

 

b) Create a folder on Yukon server, and copy remote debugger component which is shipped with Whidbey to that folder on Yukon server. (You can find all the files under Whidbey installation folder. Usually it is D:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Remote Debugger\x86)

 

c) Run msvsmon.exe on Yukon machine.

 

d) Open Whidbey to connect to a Yukon server with either

    1. a SQL account. Also make sure the user account (NT login) on the client machine is the administrator account on the Yukon server.
    2. Or a NT domain user(with NT authentication mode). Make sure that domain account has permission to run the stored procure sp_enable_sql_debug on Yukon server

e) In Server Explorer window, right click the connection node, select Allow SQL/CLR Debugging in the context menu

 

f) Now you should be able to debug CLR Procedure in Whidbey