Adding Assembly to Sql Server 2008

Oct 26, 2008 at 4:12 PM
I attempted to add this assembly to sql server 2008 and got the following error:

CREATE ASSEMBLY failed because type 'Irony.Compiler.Scanner' in safe assembly 'Irony' has a static field '_tab_arr'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

I can see the value of adding this functionality directly to Sql Server. Any suggestions on the best way to work around the above problem?

Thanks
Royal

Coordinator
Oct 27, 2008 at 2:20 AM
Hi
Thanks for finding it - never thought such a little thing could make such a big trouble. A workaround - just make it local variable of the method that follows (it's the only place it is used).
Like this:

 

char[] tab_arr = { '\t' };

(better to remove the leading underscore) I will change it in the next code drop.
Let me know if there are any more troubles

Roman

 

Oct 27, 2008 at 10:47 PM
That fixed one problem but there must be quite a few of these below is the next error

CREATE ASSEMBLY failed because method 'ResizeArrayTo' on type 'Irony.Runtime.Unassigned' in safe assembly 'Irony' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Thanks
Royal

Coordinator
Oct 28, 2008 at 6:44 AM
Hi
Can we somehow get all these violations in one list? Is there some FxCop-like setting, or special tool that would hilight all these at compile time? That's the first time I hear about these special requirement to "safe" assemblies. Can you please provide more, from SQL 2008 docs maybe?
thanks
Roman
Oct 29, 2008 at 11:51 AM
Edited Oct 29, 2008 at 12:49 PM
I tried to add your code to a sql server project in Visual Studio but Sql server doesn't have a reference to system.core. The idea was to see what errors I got when compiling in that environment. I also tried using fxcop and it wasn't able to find the system.core reference. I am new to fxcop so not sure what I did wrong. I submitted a request on a Sql Server forum about this but not getting any responses.
I did find the following article on MSDN about CLR Programming Restrictions:
http://msdn.microsoft.com/en-us/library/ms403273.aspx

Will post more later if I learn anything.

Thanks
Royal
Coordinator
Oct 29, 2008 at 5:32 PM
That link doesn't help much - it's about something different. I've tried to go through and fix all static fields, so try the latest checkin version. Let me know about any new violations.
Roman
Oct 29, 2008 at 11:27 PM
Well it looks like not all .net assemblies can be added to sql server. I got the below error when trying to add system.windows.forms.dll to sql server. I had to do this before I could add your assembly.

Warning: The Microsoft .NET Framework assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Warning: The Microsoft .NET Framework assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Msg 6218, Level 16, State 2, Line 1

CREATE ASSEMBLY for assembly 'System.Windows.Forms' failed because assembly 'System.Drawing' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

I removed all the errors because they are specific to the assembly.

Thanks or trying to make this work in Sql Server.

Royal

Coordinator
Oct 30, 2008 at 12:34 AM
Well, your bad luck - I've just added reference to Windows.Forms assembly a few days ago, to implement classes in EditorServices namespace. I didn't think it will do any harm - Windows.Forms is available on any computer with .NET installed so devs would have no problem recompiling Irony; and when used in server process, the application wouldn't invoke any classes in EditorServices namespace, so WindowsForms assembly will never be actually loaded.  
But it looks like SQL server process is a special case - SQL Server is a bit paranoic about this stuff, it wants verification of all referenced assemblies.
Let's try the following. On both files in EditorServices (EditorAdapter.cs and EditorViewAdapter.cs), set Build Action attribute to None. Remove reference to Windows Forms assembly, and rebuild Irony. Then try loading it into SQL Server. Let me know if it works
Oct 30, 2008 at 3:39 AM
Looks like another static field

CREATE ASSEMBLY failed because type 'Irony.Compiler.SymbolTerminal' in safe assembly 'Irony' has a static field '_symbols'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

Thanks
Royal

Coordinator
Oct 30, 2008 at 3:56 AM
Well, that's strange. Here's declaration line for _symbols:

private

static readonly SymbolTerminalTable _symbols = new SymbolTerminalTable();

It IS read-only. Please check you use the latest, and load the latest into SQL process. Other than that - have no idea
Roman

 

Oct 30, 2008 at 4:21 PM
I guess I grabbed the wrong version. I did find a couple more that I set to read only but now I have a new error. Don't know if I can exclude this from the build

CREATE ASSEMBLY failed because type "Irony.Runtime.SlimLock" in safe assembly "Irony" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.

Thanks
Royal

Coordinator
Oct 30, 2008 at 4:29 PM
SlimLock is not actually used, it is included prematurely. Just remove it from project and try again
Oct 30, 2008 at 5:30 PM
That did it. I was able to register the assembly and then when into my database project and added it as a reference.

Thanks
Royal
Coordinator
Oct 30, 2008 at 5:57 PM
Great.
Now I have to think how to provide SQL Server-compatible version, so you don't have to exclude EditorServices classes. Well, maybe just put them in another assembly.
Another thing, you mentioned you found couple more static fields that you made read-only - can you please tell me which ones?
Thanks
Roman
Oct 30, 2008 at 6:16 PM
Below are the 2 error messages. These are together on in the code.

CREATE ASSEMBLY failed because type 'Microsoft.Scripting.Math.BigInteger' in safe assembly 'Irony' has a static field 'maxCharsPerDigit'. Attributes of static fields in safe assemblies must be marked  readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.
CREATE ASSEMBLY failed because type 'Microsoft.Scripting.Math.BigInteger' in safe assembly 'Irony' has a static field 'groupRadixValues'. Attributes of static fields in safe assemblies must be marked  readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.

FYI. I am planning on following the sample code from http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/ to create a assembly I can add to sql server and use in a UDF to do searches. The sql version of your assembly may not require as much functionality.

 Thanks
Royal

Coordinator
Oct 31, 2008 at 4:11 AM
thanks, these are original Microsoft classes, will fix them.
Roman
Oct 31, 2008 at 11:54 AM
Edited Oct 31, 2008 at 3:34 PM
Below is the error I get when I try to use the assembly in sql server. getContainsString is my function. The error seems to be that the items referred to may leak when aborted.

Msg 6522, Level 16, State 2, Line 4

A .NET Framework error occurred during execution of user-defined routine or aggregate "getContainsString":

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All

The demanded resources were: MayLeakOnAbort

System.Security.HostProtectionException:

at Irony.StringSet..ctor()

at Irony.Compiler.Grammar..ctor()

at iFTSSearch.SearchGrammar..ctor()

at iFTSSearch.UserDefinedFunctions.getContainsString(String textInput)

Here's an explanation I found about this error
 MayLeakOnAbort Might cause a resource leak on an abort,
                       if not protected by a safe handle or some other means
                       of assuring the release of resources.

.Thanks

Royal

Coordinator
Oct 31, 2008 at 7:12 PM
Edited Oct 31, 2008 at 7:14 PM
Well, I have to throw in a towell here - not much I can do. The StringSet class is a specialization HashSet<string> of generic template Hashset, which has this troubled HostProtection attribute on it. This effectively prohibits it from using in safe assemblies in server environment. The StringSet is too deeply used in Irony to try to replace it with something. It is really frustrating to see such a general purpose "innocent" class to bear an indicator of possibility of such a host-hostile behavior.
The only thing I can suggest is to try to load Irony as unsafe assembly. In this case SQL Server bypasses all these programmatic checks. According to documentation it is SQL Server admin's choice to rely on his own judgement about assembly and tell SQL to trust it. Irony doesn't do any dangerous tricks with unmanaged code and system APIs, so probably it won't harm server in any way. And you have full source code - so no hidden surprises (trojans) there.
Roman
 
Nov 1, 2008 at 12:44 AM
Edited Nov 1, 2008 at 12:54 PM
If i set both the irony assembly and my assembly to unsafe, the code will execute with no errors. I don't know if anyone would want to have these assemblies running in unsafe mode. I will have to also look into the memory management which the warning was giving me and see if I can do anything in my code.

Thanks
Royal