W2K8 x64 and external clr function calls

[Former Member]

Can anyone verify that in SA 11.0.1 running on W2K8 x64 can make external function call to .Net C# dll? I am getting object reference error on the call into the dll. Dll has simple static function to return 1. Dll was compiled using csc /t:library /out:my.dll /platform:x64

The setup I have works if using SA 11.0.1 on W2K3 x86 with same dll recompiled for x86.
So it can't be the code.

[jeff_albion]

This configuration should work. Can you confirm which version of the database server is being launched? Is it being launched from the bin32 or the bin64 directory? Is dbextclr11.exe being launched successfully?

Can you provide the SQL procedure you used to create the external function call?

[Former Member]

The command used to launch SA database is using bin64.
When I make my function call, I see that dbextclr11.exe is launched.
My dll code in VS2008 using 2.0 Framework: namespace CLRDLL { public class StaticTest { public static int GetValue() { return 1; } } } I build the dll and copy it into the bin64 folder of SA.
My function in SA: ALTER FUNCTION "ev"."fn_clr_test"() returns integer external name 'CLRDLL.DLL::CLRDLL.StaticTest.GetValue() int' language CLR

It's important to know that the server is Win2008 R2 running x64.

[Former Member]

Error message I get is: Procedure 'fn_clr_test' terminated with unhandled exception 'Object reference not set to an instance of an object.' SQLCODE=-91, ODBC 3 State="HY000"

[Former Member]

Here is what I think might be happening in your particular case. In SQL Anywhere 11, the dbextclr11.exe is built with /platform:anycpu. The same is true for the SQL Anywhere .NET Provider which the CLR external environment makes use of. The SA .NET Provider attempts to load some native pieces, including the SQL Anywhere language dll. If the provider ends up loading the 32-bit language dll instead of the 64-bit language dll, then the framework will (most likely) expect everything to work in a 32-bit environment. Hence a dll that is built /platform:x64 will not load as a result. Have a look at your path variable and see if bin32 appears before bin64. That might be why the 32-bit language dll is being loaded. If bin32 does appear before bin64, then try switching the two around within the path and see if that solves the problem. Of course, the other thing you can do is build your dll with /platform:anycpu if possible.

Note that this problem is resolved in SQL Anywhere 12 since the dbextclr12.exe is now built specifically with /platform:x86 (for the bin32 one) and /platform:x64 (for the bin64 one).

[Former Member]

I've verified that path has SAbin64 before bin32. still get the error. I've recompiled dll using /platform:anycpu, still same problem.
I would like to know if anyone out there actually has a working combination of Windows 2008 R2 x64 server + SA 11.0.1 running x64 engine + making external CLR function call ?

Or am I just trying to do something that doesn't work?

[Former Member]

Yes, I just tried using a 64-bit SA 11 server and the CLR external environment and it worked fine. I must point out though that I had to compile my test dll using /platform:anycpu due to a build environment issue on my machine, but it did work fine.

[Former Member]

Do you have the SA 11 32-bit installed also? That is what my server configuration has both 32 and 64 SA installed. I'm wondering if that is the issue. I'm going to uninstall SA and only install the x64 SA. And see...

[Former Member]

Karim, were you running SA on Win2008 server? After installing only x64 version of SA ... Still the same error.

[Former Member]

Yes, I do have both 32 and 64 bit installations on my machine (out of necessity). I think you may have run into a valid bug. If possible, please open a support case and hopefully we will be able to reproduce and resolve the problem that you are running into.

[Former Member]

i do have a case open but was hoping that i could find someone who has actually ran into my issue on the forum. Case #11675012

[Former Member]

By the way, are you by any chance renaming your dll? It seems like the framework does not like it when you rename the dll to have a name other than what you built the dll as. For example, when I build my test dll using csc and explicitly name the dll as clrtest.dll. All works fine as long as I keep the dll named clrtest.dll. If, however, I rename the dll to be myclrtest.dll, then I suddenly start getting the error you are seeing.

[Former Member]

We think we might have solved this problem. Can you please try the following...

Instead of creating your function as:

ALTER FUNCTION "ev"."fn_clr_test"() returns integer external name
'CLRDLL.DLL::CLRDLL.StaticTest.GetValue() int' language CLR

can you instead try:

ALTER FUNCTION "ev"."fn_clr_test"() returns integer external name
'CLRDLL.dll::CLRDLL.StaticTest.GetValue() int' language CLR

Let me know if that is sufficient to work around the problem for you. In the meantime, we will try and put together a proper fix.

Karim

[Former Member]

Karim,

Thanks. That worked.

[Breck_Carter]

...whoa! I had to do a file compare to see the difference, must have SET OPTION case_blindness = 'ON' 🙂

[Former Member]

Thanks for verifying the work around. The proper fix has been put into SA 11.0.1 build 2634 and up.

Karim

[VolkerBarth]

... You're asking for age sensitivity?:)

[MCMartin]

Me too, so for all others the difference is the lowercase of the dll extension.