Why might one function run in SQL Server CLR cause a crash, while working fine in a standalone app?

These two methods below are similar, except one handles null values and the other does not. To handle null values, it uses SqlString type and checks the "get_IsNull" property.

Why might the first one be causing the error "A .NET Framework error occurred during execution 开发者_如何学运维of user-defined routine or aggregate "CheckMailingAddress": ." when run inside SQL CLR, while the second one does not?

In particular, the TSQL error is "Msg 10329, Level 16, State 49, Line 1 .Net Framework execution was aborted."

.method public hidebysig static bool CheckMailingAddress(valuetype [System.Data]System.Data.SqlTypes.SqlString param0) cil managed
{
   .maxstack 8
    L_0000: ldarga.s param0
    L_0002: nop 
    L_0003: nop 
    L_0004: call instance bool [System.Data]System.Data.SqlTypes.SqlString::get_IsNull()
    L_0009: brfalse L_0010
    L_000e: ldc.i4.1 
    L_000f: ret 
    L_0010: ldarga.s param0
    L_0012: nop 
    L_0013: nop 
    L_0014: call instance string [System.Data]System.Data.SqlTypes.SqlString::get_Value()
    L_0019: call class DatabaseValues.MailingAddress DatabaseValues.MailingAddress::op_Explicit(string)
    L_001e: pop 
    L_001f: ldc.i4.1 
    L_0020: ret 
}

.method public hidebysig static bool CheckMailingAddress(string param0) cil managed
{
   .maxstack 8
    L_0000: ldarg.0 
    L_0001: call class DatabaseValues.CheckMailingAddress DatabaseValues.CheckMailingAddress::op_Explicit(string)
    L_0006: pop 
    L_0007: ldc.i4.1 
    L_0008: ret 
}

Keep in mind, the MSIL is correct as as far as I know, because both of these methods work when called in a standalone app. It's only when called inside SQL CLR that the first of the two crashes. In the SQL CLR, the function is defined with the "nvarchar(4000)" type, which should play nice with SqlString as far as I know.

I could probably implement the first method using "string" as well and still do the null check, but it uses SqlString to take advantage of the INullable interface properties "IsNull" and "Value", because it's part of a generic code generator where other Sql* types could be used.

SIMPLE PROBLEM SUMMARY:

For those distracted by the MSIL in the method body; ignore it. I recompiled the functions to do nothing at all, and my point is that when "SqlString", rather than "string", is the input type, the CLR blows up and terminates with no error message, and the return value is NULL rather than TRUE.

//Crashes when input parameter is "SqlString"
.method public hidebysig static bool CheckMailingAddress(valuetype [System.Data]System.Data.SqlTypes.SqlString param0) cil managed
{
   .maxstack 8
    L_001f: ldc.i4.1 
    L_0020: ret 
}

//Doesn't Crash when input parameter is "string"
.method public hidebysig static bool CheckMailingAddress(string param0) cil managed
{
   .maxstack 8 
    L_0007: ldc.i4.1 
    L_0008: ret 
}

I found the source of the problem, and was able to resolve it, but I'm not sure about the details.

At some point I switched my DeployDatabaseAssembly project to target .NET 4.0, and AssemblyBuilder must have generated an assembly that targets .NET 4.0 as well. Switching the project to target .NET 3.5 fixed the problem.

What's funny is the source DLL (database.dll) that contains all my datatypes is still targetting .NET 3.5, and was left that way intentionally because I knew SQL Server only supports CLR 2.0 right now, which effectively makes it incompatible with .NET 4.0, because .NET 4.0 seems to require CLR 4.0. Using ILMerge, I was combining the dynamic assembly containing the generated functions with my existing (.NET 3.5) database.dll. This ultimately resulted in hybrid assembly .NET 4.0 assembly that was mostly based on .NET 3.5 features and classes. It's strange that I was able to get functions to work that used the basic "String" and "int" type parameters, but the SqlString type was causing crashes... obviously because it was being pulled from .NET 4.0 System.Data.dll, since it was referenced as "typeof(SqlString)" in my DeployDatabaseAssembly, which was targetting .NET 4.0. It's just weird how that was crashing without any kind of error message or without any kind of warnings about it being incompatible with the loaded SQL CLR modules.

I wish I knew a way to force AssemblyBuilder running in a .NET 4.0 app to generate an assembly targeting .NET 3.5...

Update: Problem thoroughly solved

I focused on the output of ILMerge and went ahead and switched the DeployDatabaseAssembly back to .NET 4.0. By the way, I use ILMerge as a reference in my project, since it's a .NET assembly.

By setting the ILMerge option like this:

ILMerge merger = new ILMerge();
merger.SetTargetPlatform( "v2", @"C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v3.5\Profile\Client");

The resulting DLL deploys to SQL Server (as it did before), but it actually runs without errors this time.

Interestingly, if I replace just the "v3.5" in the target platform path with "v4.0" and try to deploy the assembly to SQL Server, then I get a useful error message immediately during deployment "CREATE ASSEMBLY for assembly 'my assembly name' failed because the assembly is built for an unsupported version of the CLR runtime.". It's odd that when I wasn't setting any target platform at all, it would deploy fine, but was crashing without any error message.

This table summarizes the above configuration combinations and results: