Debugging UDF’s is not quite as simple as debugging other SharePoint artefacts. The UDF is called by the Excel Services service application which runs as a separate process. Bearing this in mind we have two options, we can either attach our debugger to all instances of the w3wp.exe process, or we can find out which process is being used to run service applications on out server.

Discovering the ProcessID used to run a User-Defined Function

Even though I’m sure that everybody will take the lazy option of attaching to all worker processes, here’s how we can find out the correct Process ID:

  1. As mentioned, Excel Services runs under a separate process. All service applications running on a server are published under the SharePoint Web Services application in IIS7 and run within a specific application pool. Knowing the name of this application pool is important when working with UDF’s since we may need to recycle it in order to free up locked assemblies.
  2. In IIS Manager, navigate to the SharePoint Web Services site, expand the root node and select the first service application.
  3. From the Actions pane, select Advanced Settings. In the General section the name of the Application Pool will be displayed as shown.
  4. Armed with the name of the application pool, we can now do a bit of digging to discover the Process ID. Open a command prompt and navigate to the C:\Windows\System32\inetsrv folder
  5. Enter the following command to list all the worker processes that are being used by IIS:
appcmd list wp

The list returned will be formatted as follows:

WP "7260" (applicationPool:SharePoint - 80)
WP "7444" (applicationPool:SharePoint Central Administration v4)
WP "4556" (applicationPool:1c549b9ed5ad4dac8e977df6da3c733b)
WP "4428" (applicationPool:ae7c416ce0ac4df7a2cfa46b8fa7327c)

The first column, WP, identifies the type of record, in our case worker process. The second column contains the process id and the last column contains the application pool that is using the process. By finding the process that corresponds to our SharePoint Web Services application pool we can determine the process id.

Note that the process id may change between debugging sessions but the application pool name will remain constant unless SharePoint is reinstalled.

Manually attaching the Visual Studio 2010 debugger

  1. In Visual Studio 2010, from the Debug menu, select Attach to Process.
  2. Select the w3wp.exe process with the correct ID value and then click on Attach.
  3. Add a breakpoint within the UDF function. Execution will stop on the breakpoint, allowing debugging to take place.

We’ve seen how to debug UDF’s using Visual Studio and we can now step into our UDF code. But what happens if we need to make changes to our code and recompile our assembly? You’d think it’d simply be a case of clicking the Build button but unfortunately it’s not quite as straightforward if we’ve configured SharePoint to pick up the UDF from our bin folder. The Excel Services service application will maintain a lock on the file while it’s in use, making it impossible for Visual Studio to overwrite when compiling a new version. Thankfully, this is an easy problem to fix. All we need to do is recycle the SharePoint Web Services application pool and the lock will be released. This can be done using IIS or using the command line by entering:

appcmd recycle apppool /apppool.name:<the name of the app pool>