September 30, 2020 at 5:05 pm
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:
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
September 30, 2020 at 6:32 pm
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.
October 1, 2020 at 1:59 pm
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:
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply