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

Question

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

quite frankly, this seems (to me at least) to be an abuse of what CLR stored procedures were meant for — Mitch Wheat
– Mitch Wheat, Commented Oct 21, 2009 at 1:09
You can call String.IsNullOrEmpty instead of checking for null and "" separately. — SLaks
– SLaks, Commented Oct 21, 2009 at 1:13
You know that String.Empty and "" are the same thing, right? Did you maybe mean for one of those to be null? — Jonathan Rupp
– Jonathan Rupp, Commented Oct 21, 2009 at 1:16
I think when I dropped in the value == "" clause, I was thinking of dealing with DBNulls and empty strings. I realize I already deal with DBNulls in another location. In one of the versions that I tested to try and work around this, the only statement in the delegate was 'return true;' and it still failed. @Mitch, if CLR procs aren't meant to do things you can't do in T-SQL, what are they for? The proc validates multiple columns in multiple tables and there's no easy way to do it in T-SQL (short of writing over 400 tests by hand). — user193485
– user193485, Commented Oct 21, 2009 at 13:10
I got this same error when trying to deploy something else where it was accessing but not actually modifying static properties. — lambacck
– lambacck, Commented Sep 20, 2011 at 19:07

Remus Rusanu · Accepted Answer · 2009-10-21 01:12:41Z

5

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.

answered Oct 21, 2009 at 1:12

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

3 Comments

Remus Rusanu Over a year ago

BTW, the mysterious static is generated because of your anonymous delegate, afaik using an explicit delegate would not require this workaround.

user193485 Over a year ago

Thanks Remus. I'll try that. What's befuddling is that there are 3 other similar functions, all using anonymous delegates to perform the test. Adding [CompilerGeneratedAttribute()] before the class decl seemed to fix the problem. Thanks!

Marc Gravell Over a year ago

This looks satisfyingly evil (in a good way). A bit of an odd use, and I think "works" would be more accurate than "is valid", but: much appreciated, thanks.

spender · Accepted Answer · 2009-10-21 01:14:58Z

0

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;

answered Oct 21, 2009 at 1:14

spender

121k36 gold badges245 silver badges369 bronze badges

Comments

FistOfFury · Accepted Answer · 2015-08-03 22:08:29Z

0

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.)

answered Aug 3, 2015 at 22:08

FistOfFury

7,2257 gold badges51 silver badges57 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related