How to put a message in the WebSphere MQ queue from SQL Server sp?

Question

Is there an API to connect to a Websphere MQ queue from a SQL Server stored procedure and send a message to the queue?

If not, what would be the best way to achieve this?

Answer 1

There is a much simpler solution than this. Check this.

http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fbc34040_.htm

Answer 2

The solution I'm going to use for this is to write a CLR stored procedure and deploy it to SQL Server.

Inside the CLR stored procedure, I will use the MQ.NET api.

Update: I created a stored procedure using the following code:

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using IBM.WMQ; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static int MQStoredProc(String queueManager, String queueName, String messageText) { //MQEnvironment.Hostname = "localhost"; //MQEnvironment.Port = 1414; //MQEnvironment.Channel = "SYSTEM.DEF.SVRCONN"; MQQueueManager mqQMgr = null; // MQQueueManager instance MQQueue mqQueue = null; // MQQueue instance try { mqQMgr = new MQQueueManager(queueManager); mqQueue = mqQMgr.AccessQueue(queueName, MQC.MQOO_OUTPUT + MQC.MQOO_FAIL_IF_QUIESCING); // open queue for output but not if MQM stopping if (messageText.Length > 0) { // put the next message to the queue MQMessage mqMsg = new MQMessage(); mqMsg.WriteString(messageText); mqMsg.Format = MQC.MQFMT_STRING; MQPutMessageOptions mqPutMsgOpts = new MQPutMessageOptions(); mqQueue.Put(mqMsg, mqPutMsgOpts); } return 0; } catch (MQException mqe) { return ((int)mqe.Reason); } finally { if (mqQueue != null) mqQueue.Close(); if (mqQMgr != null) mqQMgr.Disconnect(); } } }; 

This is not ready for production, but successfully inserts messages in the queue manager running on the same server as the SQL server in binding mode.

Answer 3

The easiest way I can think of is to write, write information to a file, and then use rfhutil to export the message to the queue. This will require manual intervention. Another option is to write a simple Java application using JMS and JDBC.

Answer 4

The .NET CLR approach would also be my suggestion. I would be curious to see some sample code, I have never tried this before! Should work, I think.