Why won't SQL Server register my assembly as SAFE?

Question

On SQL Server 2008, I'm attempting to register an assembly that seems to only reference the supported libraries. Here is the T-SQL code that I'm using to register the assembly:

create assembly MySpatial from 'c:\Spatial.dll'

This results in the following error:

Msg 6509, Level 16, State 31, Line 1 An error occurred while gathering metadata from assembly 'Spatial' with HRESULT 0x80004005.

However, if I add with permission_set=unsafe, then SQL will execute the command successfully. How can I find out why the error occurred, or why my assembly must be registered as unsafe?

Answer 1

I ran into the same thing, and after some investigation, I think it's because of optimization that the compiler does on lambda expressions. There's some overhead in creation a delegate from a lambda expression. I think it must lazy initialize a hidden static field containing the delegate the first time the lambda is accessed so it can reuse the already constructed delegate in future calls.

The reason I think this is because if the lambda captures any variables, it doesn't cause the SAFE issue. It would make sense that if it's capturing variables, you would need to create a different delegate for each time it's called, but if the lambda is completely self contained, it could cache it for efficiency purposes.

Microsoft did fix the issue in SQL 2008, but they won't fix it in SQL 2005, so if there's a need to support existing SQL 2005 installations, all it requires is eliminating "statically optimizable" lambdas, not all the lambdas in the whole assembly.