Advanced Debugging


Yes, you'll find there are times when you want to debug your CLR executable, but you don't want to rebuild and redeploy it beforehand or (more importantly) don't want to execute the test.sql test script. This can be accomplished by manually attaching Visual Studio to the target SQL Server instance hosting your CLR executable. This is not that hard to set up, and Visual Studio 2005 makes it easier than ever. There are a few surprises here that might take you off guard, so read this section carefully before wandering into the swamp.

Suppose you have a SQL Server that's hosting the CLR executable you want to test. In this case, I'm using BETAV9 as the remote system. To debug CLR executables on that system, you'll need to "attach" to the specific process on that machine that's hosting the CLR executable. To set this up, follow these steps:

1.

In the Visual Studio IDE, load the CLR executable project and click "Debug | Attach to Process" from your CLR executable project. This exposes the dialog shown in Figure 13.107.

Figure 13.107. Attaching to a selected SQL Server hosting your CLR executable.


2.

Check "Show processes from all users" to list processes being run as other users. SQL Server is not typically executed with your user credentials (or shouldn't be).

3.

Scroll down in the dialog to locate the specific instance of SQL Server hosting your CLR executable. Nope, these are not listed by instance name (I'm not sure why not), but by process name and ID. If you're not sure what process to attach to, use the SQL Server Configuration Manager to identify the ProcessID for the specific instance hosing your CLR executable.

Note that the target process must have T-SQL listed as an attachable "type". This is not included in the list if CLR executables have not been enabled on the specific SQL Server instance.

4.

Click "Attach" to begin the attachment process. At this point, you'll see a dialog, shown in Figure 13.108, that warns you about potential harm to your computer. If you feel lucky, click Attach.

Figure 13.108. The Attach Security warning.


5.

At this point, the current instance of Visual Studio 2005 and every other instance of Visual Studio 2005 is now in debug mode. Right, that's what I said. Attaching to a process when you have multiple instances of Visual Studio started switches all of them into debug mode. And no, once you attach to a process for debugging, you can't attach another instance of Visual Studio to that server instance. Strange.

6.

Now that you're in Debug mode, you can set/reset breakpoints and view the state of specific variables, but you can't edit and continue. To test your code, you can execute queries directly against the target SQL Server. If the T-SQL accesses your CLR executable and you've set a breakpoint in the CLR code, the Visual Studio IDE is topped and you'll be in step-through mode.

Since you'll be interactively debugging SQL Server and CLR executables, keep in mind that the test query you're executing could time out and confuse your testing. Use the Tools | Options | Query Execution dialog in SQL Server Management Studio to set the Execution time-out value. It defaults to 0 (infinite timeout), so this should not be a problem unless you've changed it.

It's also important to note that all CLR executables on the instance are paused when you step into your test CLR executable. This means it's especially important to avoid debugging on a production server.


When you're done debugging, simply click the "Stop Debugging" button in the Visual Studio IDE. This detaches the server from your code.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net