C# CLR Stored Proc won't Deploy to SQL Server 2005

I'm developing a C# SQL Server 2005 stored procedure that does data validation for my application. I have a nice framework built, that is working. The validation methods are along the lines of:

private void TestDate() {
    TestFields(delegate (string value) {
        if (value == String.Empty || value == "")
            return true;
        return IsDate(value);
    });
}

The solution compiles, deploys, and runs fine with several methods written like the above. TestFields iterates over the columns returned by a query, calling the delegate that returns whether or not the validity test is passed. I added a new method:

private void TestRequired() {
    TestFields(delegate (string value) {
        return ! (value == String.Empty || value == "");
    });
}

With this method, the DLL won't deploy: CREATE ASSEMBLY failed because method 'TestRequired' on type 'SurveyValidator' in safe assembly 'SurveyValidation' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

I'm pulling out my hair. If I comment out TestRequired(), it works. Obviously, it's not doing an assignment statement, so I don't 开发者_开发技巧know what SQL Server is complaining about. Did I just stumble onto some kind of obscure bug? (I know what the error means, I don't have any static fields in the SP class. Just the static entry method that creating the project gives you.)

TIA, Dave

Add a CompilerGeneratedAttribute in front of your class declaration:

This attribute allows SQL server to reference compiler-generated static values.

I know it sounds a bit unorthodox, but is perfectly OK to do this.

I'll qualify this answer be saying I'm not too familiar with CLR SPs

Isn't value == String.Empty || value == "" a tautology, effectively testing the same thing twice? String.IsNullOrEmpty seems like a better option (however, that's another static reference... read on).

Counter to this, I'd be inclined to remove any static references. String.Empty seems like a potential candidate for this problem, so I'd remove it at least to test if it is causing the problem. So perhaps the test could be changed to only:

return value != "";

or perhaps:

return value != "" && value != null;

I had a similar issue and changed the Permission Set property of the assembly to "Unsafe". This allowed me to deploy the CLR code to the SQL server without any issues.

Note that my wrapper assembly also had to be deployed using Unsafe in order to ensure "Full trust" privileges. (Was un-intuitive to me at first that Unsafe assemblies get full trust.)