SQL Server 2016 CLR Stored Procedure Error : "A system assertion check has failed"

Question

I am attempting to run a Native Code API (in a C++ .dll) via a C# assembly so that I can use certain API functions in a CLR Stored Procedure in SQL Server. The function that I am attempting to use from the C++ dll accesses raw data from a data historian and returns the data in unmanaged types. It is then left to the C# assembly to marshal and pipe results to the SQL Server.

I do not have the source code for the C++ dll, so I really do not know what exactly is going on underneath the hood (it is third-party). However, I can access these API functions in a C# console app without issue (I relied on https://lennilobel.wordpress.com/2014/01/29/calling-c-from-sql-clr-c-code/ for wrapping the C++ dll in .NET). I developed a working C# console app, then turned this into a Class Library, wrapped the class in "[Microsoft.SqlServer.Server.SqlProcedure]" and added the assembly to the desired SQL database in UNSAFE mode. I have also made sure that clr is enabled in the SQL server and TRUSTWORTHY is off in the database that I am using.

However, I get the following issue when I attempt to call the stored procedure that uses the C# assembly.

Location:    AppDomain.cpp:2705
Expression:  hr != E_POINTER
SPID:        66
Process ID:  3584
Msg 3624, Level 20, State 1, Procedure sp_direct_proficy_api, Line 0 [Batch Start Line 2]
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
Msg 596, Level 21, State 1, Line 2
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 2
A severe error occurred on the current command.  The results, if any, should be discarded.

I have done some google searches on system assertion checks and have seen that they usually occur as a result of database corruption. I have ran DBCC CHECKDB and everything looks fine, so this is not the issue. I have replicated Leonard's example (from the above link) that is essentially the same process that I am undertaking with a much simpler C++ dll. No errors occurred with that example, so I believe that there is some competition for an appdomain between SQL Server and the C++ API.

My Question

Is this an expected issue for what I am attempting to do? I don't know a whole lot about how SQL Server accesses computer memory and claims appdomains when using CLR Stored Procedures, but it seems as if there is some harmful resource competition between SQL Server and the C++ API.

Shown below are two parts of the C# assembly (the call to the C++ dll from the C# harness and the class to be accessed by the stored procedure).

C# DLL Import From C++

public class IHUAPI
{
const string DLLNAME = "IHUAPI.dll";

static class IHU64
{

    [DllImport(DLLNAME, CallingConvention = CallingConvention.StdCall, EntryPoint = "ihuConnect@16")]
    public static extern ihuErrorCode ihuConnect(string server, string username, string password, out int serverhandle);

    [DllImport(DLLNAME, CallingConvention = CallingConvention.StdCall, EntryPoint = "ihuReadRawDataByTime")]
    public static extern ihuErrorCode ihuReadRawDataByTime(int serverhandle, string tagname, ref IHU_TIMESTAMP start, ref IHU_TIMESTAMP end, out int numberOfSamples, [MarshalAs(UnmanagedType.LPArray, SizeParamIndex = 4, ArraySubType = UnmanagedType.LPStruct)] out IHU_DATA_SAMPLE[] samples);
}

public static ihuErrorCode ihuConnect(string server, string username, string password, out int serverhandle)
{

        return IHU64.ihuConnect(server, username, password, out serverhandle);
}

public static ihuErrorCode ihuReadRawDataByTime(int serverhandle, string tagname, IHU_TIMESTAMP start, IHU_TIMESTAMP end, out IHU_DATA_SAMPLE[] samples)
{
        int numberOfSamples;
        return IHU64.ihuReadRawDataByTime(serverhandle, tagname, ref start, ref end, out numberOfSamples, out samples);
}
}

C# Assembly Used in Stored Procedure to Access C++ API

[Microsoft.SqlServer.Server.SqlProcedure]
public static void API_Query(string tagname, DateTime start_date, DateTime end_date)
{

    int handle;
    ihuErrorCode result;
    result = IHUAPI.ihuConnect("houmseosprf007", "", "", out handle);
    IHU_DATA_SAMPLE[] values;
    IHU_TIMESTAMP start = new IHU_TIMESTAMP(start_date);
    IHU_TIMESTAMP end = new IHU_TIMESTAMP(end_date);

    ihuErrorCode result_api = IHUAPI.ihuReadRawDataByTime(handle, tagname, start, end, out values);

    SqlMetaData[] md = new SqlMetaData[3];
    md[0] = new SqlMetaData("tagname", SqlDbType.Text);
    md[1] = new SqlMetaData("return_value", SqlDbType.NVarChar, 50);
    md[2] = new SqlMetaData("timestamp", SqlDbType.DateTime);
    SqlDataRecord row = new SqlDataRecord(md);
    SqlContext.Pipe.SendResultsStart(row);

    DateTime p;
    string p2;

    for (int i = 1; i < (values == null ? 0 : values.Length); i++)
    {

        using (IHU_DATA_SAMPLE sample = values[i])
        {
            if (sample.ValueDataType != ihuDataType.Array)
            {
                p = sample.TimeStamp.ToDateTime();
                p2 = sample.ValueObject.ToString();
                row.SetValue(0, tagname);
                row.SetValue(1, p2);

                row.SetValue(2, p);

            }
            else
            {

                p = sample.TimeStamp.ToDateTime();
                ihuArrayValue aValue = (ihuArrayValue)Marshal.PtrToStructure(sample.Value.ArrayPtr, typeof(ihuArrayValue));
                p2 = aValue.GetArrayValue.ToString();
                row.SetValue(0, tagname);
                row.SetValue(1, p2);
                row.SetValue(2, p);


            }
        }

        SqlContext.Pipe.SendResultsRow(row);
    }

    SqlContext.Pipe.SendResultsEnd();
}

Answer 1

Is this an expected issue for what I am attempting to do?

I wouldn't say "expected" so much as "not unexpected", or "shouldn't be surprised by". That 3rd party library is clearly doing something that is fine when it is isolated, but unacceptable when it is initiated from within SQL Server's CLR host. There is good reason for SQL Server's CLR host to be as highly restricted as it is.

So, what you should do instead is host this 3rd party C++ library and your original (and working) C# wrapper as a Web Service running on the server that hosts the service that you are connecting to, "IHU". Then, for your SQLCLR code, use HttpWebRequest and HttpWebResponse to call that web service. Parse the response XML / JSON in the SendResultsRow() loop.

Be sure to set the PERMISSION_SET of the updated SQLCLR code to be just EXTERNAL_ACCESS as you won't need UNSAFE :-) , and you still get fairly instantaneous response in your query batch without needing to shell out to call a command-line via xp_cmdshell or invoking SSIS or even scheduling a job to do it.