3

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 
}
Improve this question
22
  • Have you tried attaching the Visual Studio debugger to this? Also, could you include the code in the original language (ie, C#, VB, etc), rather than MSIL? Commented May 3, 2011 at 21:14
  • MSIL is the original language. Yes, I've attached the debugger. It fails with no specific error message beyond what I've given and it doesn't hit any kind of breakpoint inside the CLR. Commented May 3, 2011 at 21:51
  • 1
    Wow dude - you're writing CLR procs in MSIL? Can I ask why? Have you tried having a look at what some compiled C# that does the same thing? Commented May 3, 2011 at 22:29
  • Yep! Unified data classes for apps and database. Single point of maintenance + speedy SQL CLR check constraint functions. Commented May 3, 2011 at 22:39
  • 1
    @Triynko: if it seems too hard, it usually is. Commented May 4, 2011 at 13:50

1 Answer 1

Reset to default
5

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

It doesn't surprise me that the problem ended up being outside of the code that you posted. But as a side note, have you considered the fact that someone else will likely have to maintain this? Unless your performance requirements are that it's gotta be absolute-killer speed, I'd recommend doing it in C# for the simple fact that there are more people who can read/write C# than can read/write MSIL. You might be leaving a pretty big chunk of incredibly hard to understand code for your company's next developer. Then again, if it's really a requirement, that's the price that has to be paid.
It's gotta be absolute-killer speed, and emitting MSIL directly ensures there's no compiler in the mix injecting less-than-optimal code under any circumstance. The program is actually written entirely in C#, and uses the ILGenerator class and its Emit method to emit MSIL op-codes (System.Reflection.Emit.OpCodes enum), so one never actually has to deal with MSIL syntax. The posted MSIL was obtained by opening the generated assembly in .NET Reflector.
As for consideration for other developers... do you realize that the whole purpose of this utility is to make things easier for them. While the utility itself is complicated, it's not something that's meant to be updated, because it's really a finished development tool and it's something I'd personally support or release as open-source. It wraps a lot of complicated functionality relating to querying various types of functions in SQL Server, creating those functions, handling mappings from .NET to SQL data types, deploying assemblies, creating/disabling/enabling/checking constraints, etc.
With this utility, developers can create a new data class with a header like this: "public sealed class Username: RegexConstrainedString", and tag it with an attribute like this "[SqlFunctionCheck(Table="Users",Field="Username")]". Then, by simply running this utility, it will automatically do the following for them: generate a new optimized assembly, deploy it to the database replacing old assembly, create "CheckUsername" SQLCLR function, create check constraint on the table (combining calls to that function and any others tagged to be applied to the same table), and recheck all constraints.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.