SQLCLR assembly registration failed (Type load failed)

Question

I'm trying to register SQLCLR assembly in SqlServer

CREATE ASSEMBLY [DatabaseCLR]
FROM 'T:\DatabaseCLR.dll'
WITH PERMISSION_SET = SAFE
GO

but during registration I get error message

Msg 6218, Level 16, State 2, Line 1 CREATE ASSEMBLY for assembly 'DatabaseCLR' failed because assembly 'DatabaseCLR' 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
[ : DatabaseCLR.BinaryUtils::HasSetBits][mdToken=0x6000039] Type load failed.
[token 0x02000008] Type load failed.

which is similar to one described in this question. However the situation is a bit different. In my assembly I do not use User-Defined Types.

If I usePERMISSION_SET = UNSAFE the assembly registers successfully. It doesn't seem that I use unsafe code though ("Allow unsafe code" checkbox is not checked in project properties) (or do I?).

The assembly code (simplified) is:

using System;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

namespace DatabaseCLR
{
    public class BinaryUtils
    {
        [SqlFunction(Name = "BinaryHasSetBits", IsDeterministic = true, IsPrecise = true)]
        public static SqlBoolean HasSetBits(SqlBytes data)
        {
            if (data.IsNull)
                return SqlBoolean.Null;

            if (data.Storage != StorageState.Buffer)
                throw new NotSupportedException(string.Format("Storage type {0} is not supported.", data.Storage));

            long
                len = data.Length,
                ulen = len / sizeof(ulong),
                tail = len % sizeof(ulong);

            ByteToUlongConverter conv = new ByteToUlongConverter(data.Buffer);
            for (long i = 0; i < ulen; i++)
                if (conv.ulongs[i] != 0)
                    return SqlBoolean.True;

            for (long i = len - tail; i < len; i++)
                if (data.Buffer[i] != 0)
                    return SqlBoolean.True;

            return SqlBoolean.False;
        }
    }

    [StructLayout(LayoutKind.Explicit)]
    internal struct ByteToUlongConverter
    {
        [FieldOffset(0)]
        public byte[] bytes;

        [FieldOffset(0)]
        public ulong[] ulongs;

        public ByteToUlongConverter(byte[] bytes)
        {
            this.ulongs = null;
            this.bytes = bytes;
        }
    }
}

The assembly provides functions for bitwise operations on binary types. I'm using struct with [StructLayout(LayoutKind.Explicit)] attribute for casting byte[] array to ulong[] array (to speed up processing). I guess that use of StructLayout causes error as in the related question. However it is not on UDT, and I do not see how I can fix it in this case.

Are there any chances to register assembly with PERMISSION_SET = SAFE ?

I do register my sample function as

CREATE FUNCTION dbo.BinaryHasSetBits
(
    @data varbinary(8000)
)
RETURNS BIT
AS EXTERNAL NAME [DatabaseCLR].[DatabaseCLR.BinaryUtils].[HasSetBits]
GO

I'm using x64 editions of

SqlServer 2014 (assembly compiled for .Net 4.0)
SqlServer 2008 (assembly compiled for .Net 2.0)

Do you need the using interopServices? Do you need structLayout and FieldLayout? — Solomon Rutzky
– Solomon Rutzky, Commented Nov 11, 2016 at 8:34
Better tag it, some sqlclr errors are due to some incompatibility between sql server version and .net version. — McNets
– McNets, Commented Nov 11, 2016 at 9:15
First of all - yes, your code us actually unsafe, possibility to violate checkbox is just compiler feature. The second - why you think your solution will be faster than searching in bytes array, something like Array.Exists(data.Buffer,b=>b!=0) — vitalygolub
– vitalygolub, Commented Nov 11, 2016 at 9:16
@vitalygolub, mscorlib is in the supported list, so I'm surprised that I should use PERMISSION_SET=UNSAFE. I did tested two approaches under Profiler (treating byte[] as ulong[] allows data to be processed faster than treating byte[] as it is). — i-one
– i-one, Commented Nov 11, 2016 at 10:04

i-one · Accepted Answer · 2019-11-07 14:48:55Z

1

The error is due to use of LayoutKind.Explicit.

Changing it to LayoutKind.Sequential

[StructLayout(LayoutKind.Sequential)]
internal struct ByteToUlongConverter
{
    ...
}

makes possible registering assembly with PERMISSION_SET = SAFE.

But using LayoutKind.Sequential instead of LayoutKind.Explicit breaks semantics in this case.

So, PERMISSION_SET = SAFE or LayoutKind.Explicit, not both.

answered Nov 7, 2019 at 14:48

i-one

5,1201 gold badge33 silver badges44 bronze badges

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

Collectives™ on Stack Overflow

SQLCLR assembly registration failed (Type load failed)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related