SQL Server Add external Assembly basic operations

Summary: Sometimes SQL Server built-in functions are not so useful, you can reference the external assembly, under the ugly, do add external assembly operations

1, prepare the program, compile a myclr DLL.

public class Clrclass    {        [Microsoft.SqlServer.Server.SqlFunction] public        static int myfun (int a, int b)   //must use static method, non-static method will error.        {            return a <= b? a:b;//is actually a judging minimum function        }    }

2. Add an assembly.

CREATE ASSEMBLY MyClr1 from    ' I:\Test\MyClr\MyClr\bin\Release\MyClr.dll ' with         permission_set = safe/external Access/unsafe; --(default recommended/access to external resources/unrestricted access to resources)

　　

• For assemblies created with the SAFE or EXTERNAL access permission set:
• The assembly code should be type-safe. Type safety is established by running the common Language Runtime Validation tool on an assembly.
• Assemblies should not contain any static data members in their classes unless they are marked as read-only.
• A class in an assembly cannot contain a finalizer method.

The unsafe mode is an access resource that is not subject to any restrictions.

This is a limitation on the online manual to face these patterns.

If there is a static variable, it will be an error in safe mode.

public class Clrclass    {        static int i = 0;        [Microsoft.SqlServer.Server.SqlFunction]        public static int Myfun (int a, int b)   //must use static method, non-static method.        {            i + = 1;            Return a <= b? a:b;        }

Then the database executes

ALTER ASSEMBLY myclr1from ' I:\Test\MyClr\MyClr\bin\Release\MyClr.dll ' with permission_set = Safe;

Ding-ding, winning immediately

Workaround. Remove the static variable, change the static variable to readonly mode, or change the assembly to unsafe mode.

Removing static variables and readonly is not a word. Change to unsafe mode, you can execute the following statement

ALTER DATABASE TestDB set trustworthy on;     --The database owner (DBO) has unsafe ASSEMBLY permissions and the database has trustworthy database properties, or the assembly is signed with a certificate or asymmetric key whose corresponding login has unsafe ASSEMBLY permissions Goalter ASSEMBLY myclr1from ' I:\Test\MyClr\MyClr\bin\Release\MyClr.dll ' with permission_set = unsafe;go--successful execution

You create an assembly, and then you add a function map to use the

Add function mappings Create functions Clrfbitcontains (@a as int, @b as int) returns INTAS  EXTERNAL NAME MyClr1.CLRClass.MyFun Confirm whether you need to hit Open CLR Execute permissions exec sp_configure ' clr enabled ', ' 1 ' reconfigureselect dbo. Clrfbitcontains (1,3) PS: Each time you modify the dynamic library, you need to re-execute the assembly definition to update the synchronization up to create ASSEMBLY MyClr1 from    ' I:\Test\MyClr\MyClr\bin\ Release\myclr.dll '

　　

SQL Server Add external Assembly basic operations