UDFs in the world of 64bit Excel Services installations
Deepak posted a question on one of my posts and I thought that it was worth posting a blog entry about.
I will try to break this down as simply as I can, since there are many permutations and it is not always obvious what's going on. There are also a couple of things one needs to understand before going forward:
Managed Assemblies:
When compiling a managed assembly, the developer has two basic options - they can either compile the DLL as platform agnostic or they can target it to a specific platform.
Platform Agnostic: When compiled thusly, the runtime will JIT the assembly into what it deems to be the "correct" platform. For example, if the assembly is an executable and a user double-clicks it on an x64 machine, it will run as a 64bit application. If the assembly is a DLL and is loaded into a 64bit process, it will be JITted into x64 as well. Similarly, if the process is a 32bit process running on an x64 machine, and it loads a managed DLL, the runtime will correctly JIT it to x86, allowing the process to use it.
Platform Specific: When compiled to a specific platform, the DLL will fail to load in any platform that it does not fit. In the 3 examples above, say we decided to target our binary at x86 instead of platform agnostic. The first example (double-clicking on an executable) will run our process in 32 bit compatibility mode. The second example (loading the assembly into a 64bit process) will fail because Windows does not support mixing binaries targeting different platform. The 3rd option (a 32 bit process loading the assembly) will work as expected.
Unmanaged Assemblies:
For unmanaged assemblies, the story is simpler - there is no agnostic setting* - all binaries are compiled to one platform or the other. And the same limitation as before applies - no mixing of platforms. So if you process is 64bit, you will only be able to load 64bit native DLLs. If your process is 32bit, you will only be able to load 32bit native DLLs.
Okay, but how does this apply to UDFs?
When Excel Services loads a UDF, it runs under the same limitation as any process does. That is to say, if your Excel Services process is running as a 64bit process, it will only be able to load 64bit DLLs. Since most .NET assemblies are agnostic, this is not an issue. Similarly, if your Excel Services process is running as a 32bit process, it will only be able to load 32bit processes (even when you are running on the x64 platform).
So what's the problem?
This story becomes a little hairy when your UDF DLL tries to load native DLLs. While your UDF DLL is (most probably) agnostic and, thus, will load for you seamlessly no matter what platform you are on, the DLLs it relies on may not be.
Typically, this is what users go through when they hit these issues:
1. Users install Excel Services on a 32bit machine to play around with.
2. They absolutely adore Excel Services and thus start looking for ways to play with it. They discover UDFs.
3. The user now realizes that they can use older native DLLs to make their life easier (since the code is already written).
4. Wrapping the native old 32bit DLL with some managed code is easy and works just great.
5. The user decides that they want to use the full power of the x64 platform and install the x64 version of Excel Services.
6. Once there, the UDFs suddenly stop working.
7. Users get (rightfully) upset.
What happens is that the native old 32bit DLLs will not be able to load into the 64bit Excel Services process. When Excel Services loads the UDF DLL, it gets JITted to 64bit properly. However, when that DLL tries to load the 32bit DLLs, it fails to load and cannot be used by Excel Services.
I am one of these users. What can I do then?
There are very few things you can do, sadly. Usually, none of them are easy.
1. You can recompile the C/C++ DLL into x64 and load that version from your managed UDF: A lot of times this is impossible to do (sometimes you dont have the code or the expertise).
2. You can rewrite the library in managed: This will make sure the problem is no longer there, but it also means potentially a lot of work (and is sometimes impossible because you may not know what's really going on in there).
3. Out Of Proc solutions: Multiple solutions can be thought up of how to activate the 32bit library in a separate process. However, this can be a relatively expensive thing to do and, depending on the scenario, can be too slow to be a realistic solution.
------
* Well, you can make an EXE that will load natively on both x64 and x86, but I don't think you can apply the same trick to DLLs.