Issue with MSSQL 2017 CLR referencing IBM.MQ (amqmdnetstd.dll)

  • DHL-John.Parker

    Old Hand

    Points: 342

    All,

    I've been going at this for a couple of days now and hoping someone out here has experienced this and knows how to solve it.  I am migrating a SQL Server 2008 R2 database running on Windows Server '08 to a new SQL 2017 Enterprise installation running on Windows Server '16.  In my current database, I have a CLR I developed many years ago to facilitate coms with IBM MQ servers.  This CLR uses the amqmdnet assembly from IBM.  I went the CLR route because most of my applications that that would need to use MQ to get or put messages are web based and SQL Server gave me a clean platform to facilitate those interactions (websites were already heavily interacting with the database.)  This CLR has worked great and given me no issues over the years.

    Fast forward to now.  The original assembly, amqmdnet, seems to be incompatible with SQL Server 2017 which is fine... it is depreciated anyway.  The new assembly is amqmdnetstd.dll and is built on netstandard v2.  I have been able to successfully rebuild my CLR assembly with the new MQ assembly and required netstandard.dll and .NET assemblies.  I loaded the latest .net assemblies (4.8) and ensured that netstandard was version 2.  My test CLR is a bare bones MQ PUT function to limit possible fail points to a minimum.  I can run this exact code in a console application with no issues, and I can run it on a website hosted on the same server with no issues.  Its only on the SQL Server installation that it fails.

    If I remove any try/catch on the CLR, I get the following stack trace:

    Msg 6522, Level 16, State 1, Procedure sp_clr_MQ_SimplePut, Line 0 [Batch Start Line 25]
    A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_clr_MQ_SimplePut":
    System.TypeInitializationException: The type initializer for 'IBM.WMQ.MQQueueManager' threw an exception. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: The path is not of a legal form.
    System.ArgumentException:
    at System.IO.Path.LegacyNormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
    at System.IO.Path.InternalGetDirectoryName(String path)
    at IBM.WMQ.ManagedCommonServices..ctor()
    System.Reflection.TargetInvocationException:
    at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
    at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
    at System.Activator.CreateInstance(Type type, Boolean nonPublic)
    at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
    at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
    at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at IBM.WMQ.CommonServices.CreateCommonServices()
    at IBM.WMQ.CommonServices.TraceConstructor(String objectId, String sccsid)
    at IBM.WMQ.Nmqi.NmqiEnvironment..ctor(NmqiPropertyHandler nmqiPropertyHandler)
    ...
    System.TypeInitializationException:
    at IBM.WMQ.MQQueueManager..ct...

    Here is the full CLR with only the connection details redacted:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using System.Collections;
    using IBM.WMQ;

    public partial class StoredProcedures
    {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void sp_MQ_SimplePut (SqlString strMessage)
    {
    //Conection Details
    string strHostName = "xxxxxxxx.xxx.xxx";
    int intPortNumber = xxxx;
    string strChannel = "xxxxxx.xxxxxxx";
    string strQueueManager = "xxxxxxxxx";
    string strQueueName = "xxxxxxxxxxxx";

    //Create Queue Manager Object
    MQQueueManager mqQMgr = null; // MQQueueManager instance


    //Create Connection Properties
    Hashtable mqProperties = new Hashtable
    {
    { MQC.TRANSPORT_PROPERTY, MQC.TRANSPORT_MQSERIES_MANAGED }
    ,{ MQC.HOST_NAME_PROPERTY, strHostName }
    ,{ MQC.PORT_PROPERTY, intPortNumber }
    ,{ MQC.CHANNEL_PROPERTY, strChannel }
    };

    //Create Queue Manager Object
    mqQMgr = new MQQueueManager(strQueueManager, mqProperties); //<-- This is where the exception is thrown

    //Create Queue Opening Options
    const int openOptions = MQC.MQOO_OUTPUT + MQC.MQOO_FAIL_IF_QUIESCING;

    using (MQQueue mqTargetQueue = mqQMgr.AccessQueue(strQueueName, openOptions))
    {
    // Define a WebSphere MQ message, writing some text in UTF format
    var msg = new MQMessage
    {
    CharacterSet = 1208
    };

    msg.WriteString(strMessage.ToString());

    // Specify the message options
    var pmo = new MQPutMessageOptions(); // accept the defaults

    // Put the message on the queue
    mqTargetQueue.Put(msg, pmo);

    }

    //Close Queue Connection
    if (mqQMgr != null)
    mqQMgr.Disconnect();

    }
    }

    My troubleshooting steps so far:

    • I can run this exact code with no issues on both console app and asp.net site (ASP.NET site running on same server as the MSSQL installation.)  Code doesn't appear to be the issue.
    • When I load import this CLR assembly into the dababase, I also load the exact same assembly files referenced when I built the CLR.
    • I created a new, clean database for this, so no carry over from a restored DB.
    • This database is set as TRUSTWORTHY so the CLR Strict Security feature isn't going to cause any issues, plus I've tested this with Strict Security turned off with the same results.
    • I have installed the latest .NET runtime (4.8) on the server itself.
    • I have installed IBM.WMQ Client on the server.
    • I have explicitly registered the amqmdnetstd.dll and netstandard.dll assemblies in the GAC.

    If I put a try/catch in it gives me a less detailed exception which I've put below:

    Exception.Message:
    The type initializer for 'IBM.WMQ.MQQueueManager' threw an exception.

    Exception.StackTrace:
    at IBM.WMQ.MQQueueManager..ctor(String queueManagerName, Hashtable properties)
    at StoredProcedures.sp_MQ_SimplePut(SqlString strMessage)

    Exception.Source:
    amqmdnetstd

    Exception.GetType:
    System.TypeInitializationException

    So that's where I am.  Everything points back to this being something with how SQL Server 2017 is interacting with this particular assembly.  Hoping its just a ID10T error and someone on here says "Oh yeah, you just forgot to do _____!"  One can hope, right?

    Anyone encounter this before?  Any suggestions?

    Thanks!

    John

    • This topic was modified 3 weeks, 5 days ago by  DHL-John.Parker. Reason: The Stack Trace Code didn't display properly in the post
  • DHL-John.Parker

    Old Hand

    Points: 342

    As info, the Stack Trace shows up as blank for me if I have the "Experimental Code Highlighting" enabled on my profile...  Turning it off allowed the stack track to display correctly.

  • Solomon Rutzky

    SSCoach

    Points: 16271

    This issue was cross-posted to Ask.SSC:

    https://ask.sqlservercentral.com/answers/156802/view.html

    Based on the discussion there, this appears to be an issue with the IBM assembly attempting to get the directory name of where the assembly is currently physically located. However, since SQL Server's CLR host is not file system-based, the Location property of the currently executing assembly is an empty string.

     

    For the moment, my two proposed workarounds are:

    1. Use Reflection to update the value of Location before the IBM assembly calls that method, OR maybe updating the IBM method to supply the desired value for that path, based on how it is being used. I'm not sure if either can actually be done, or if the latter can be done, if it would even solve the problem due to how that path is being used.
    2. Use COM (which requires UNSAFE but you're already there anyway) to have the assembly within SQLCLR call a method in a companion assembly (COM visible) sitting on the file system that calls that IBM code (and would have a file system Location). Not the most elegant, nor most performant, of approaches, but might could work.

     

    Take care,

    Solomon...

     

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply