#VALUE errors for database queries

Aug 16, 2011 at 3:48 PM
Edited Aug 16, 2011 at 6:57 PM

Hi,

I've written some UDFs to wrap simple database calls. These are very small queries and return immediately when run through SQL Server or via VBA/ADO. The UDFs work perfectly for me and every user on my team, but consistently return #VALUE errors for a remote user located in CA (3000 miles away).

I am trying to troubleshoot the issue, but am curious whether anyone has encountered a case where a UDF times out or, if not, has any idea what else might cause a #VALUE error to be returned from Excel-DNA?

I have the following code in my add-in to catch unhandled exceptions:

public void AutoOpen()
{
   Integration.RegisterUnhandledExceptionHandler(ErrorHandler);
}

private static object ErrorHandler(object exceptionObject)
{
   return "Error: " + exceptionObject;
}

Also worth noting is that any non-database calls (e.g. a test function to add two numbers) works fine for this remote user. So it does not seem to be a general Excel-DNA installation issue. I have verified that this user can run the query outside of Excel-DNA, so it is not a database permissions issue.

Any suggestions or hints for trying to debug would be greatly appreciated. Thank you.

Coordinator
Aug 16, 2011 at 4:55 PM

Hi Ben,

The UDF itself cannot timeout. (You can test this with a UDF that calls Thread.Sleep(10000) or longer.

Getting back #VALUE means either an unhandled exception is being thrown , or the value returned is an unknown type. Since you have an unhandled exception handler installed, I presume it is the latter case.
(Though I'm a bit worried about the unhandled exception handling - I haven't tested it recently.)

To make clearer what might be going wrong in your function, I suggest you wrap the function in its own exception handler, and return some exception detail directly if an exception is thrown. And if an exception is not thrown, return something like result.GetType().ToString(). That will return a string but show you what the actual type is - I'm guessing the faraway case is returning some unexpected type of object.

If that's not it, I'm sure I can code up with some more debugging ideas.

-Govert

Aug 16, 2011 at 5:32 PM

Thanks Govert. I have followed your suggestion to wrap the function in its own exception handler and it returned the following exception detail:

Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

It seems this may have something to do with CAS security policy and should hopefully be easy for me to fix. In terms of Excel-DNA and the #VALUE errors, I guess it also means that there's some issue with the registration of the "general" unhandled exception handler. Not a big deal as it's easy enough to wrap each function in its own exception handler, but if any other users experience a similar issue this is good to know.

Thanks again for helping me.

-Ben

Coordinator
Aug 16, 2011 at 6:13 PM

Hi Ben,

Could your UnhandledExceptionHandler be throwing an exception, trying to call ToString() on that object.

You might like to wrap you UnhandledExceptionHandler in a try-catch to make sure.

Otherwise I'd be interested, since my simple test of the UnhandledExceptionHandler suggests that it works fine, and it's actually a pain to have the exception handler everywhere.

 

Anyway - why are you getting the error?

* Which Excel-DNA version are you using - security stuff changed betweeen 0.27, 0.28 and 0.29.

* Is the Excel-DNA add-in or any of the .dlls being loading over the network (LAN or internet)?

 

Otherwise I guess you just have a permissions / login issue with the connection that your distant user makes to your database. How that exception get dealt with is a bit of a problem....not sure what's going on.

-Govert

Aug 16, 2011 at 6:30 PM

Hi Govert,

I will try wrapping the UnhandledExceptionHandler in a try-catch. Seems unlikely that ToString() would cause an exception unless the exceptionObject is null. Anyhow, I will report back once I get more information.

In terms of the error itself, I am using Excel-DNA 0.29. The add-in as well as the DLLs it references all reside on our LAN. I found that adding RuntimeVersion="v4.0" to the DnaLibrary tag solved my problem. I assume this is because .NET 4 ignores CAS policy.

-Ben

Coordinator
Aug 16, 2011 at 6:45 PM
Edited Aug 16, 2011 at 6:46 PM

Hi Ben,

OK - the LAN story explains the security issues. I'm guessing the CAS security work differently from you remote user - something might be set up differently in the CAS policy.

I changed some of the security options in 0.29, reverting in some cases to the behaviour in version 0.27. All is indeed better under .NET 4.0

If you need to run under .NET 2.0, with version 0.29, you can try a flag in the .dna file that controls some of the AppDomain stuff (and enabled the behaviour as it is in versino 0.28).

<DnaLibrary CreateSandboxedAppDomain="true" >

or

<DnaLibrary CreateSandboxedAppDomain="false" >

The code that checks works like this:

 

// Default sandboxedAppDomain options
if (createSandboxedAppDomainValue.CompareNoCase(L"true") == 0)
{
	createSandboxedAppDomain = true;
}
else if (createSandboxedAppDomainValue.CompareNoCase(L"false") == 0)
{
	createSandboxedAppDomain = false;
}
else
{
	// Default => true under .NET >= 4.0, else false
	if (clrVersion.CompareNoCase(L"v4.0") >= 0)
		createSandboxedAppDomain = true;
	else
		createSandboxedAppDomain = false;
}

 

I'd be interested to hear what you get with the different options under the different runtimes.

-Govert