| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Doing MQI calls from Microsoft SQL Server |
« View previous topic :: View next topic » |
| Author |
Message
|
| IM0547 |
 Posted: Fri Jul 27, 2001 10:34 am Post subject: |
 |
|
Newbie
Joined: 26 Jul 2001
Posts: 3
|
Does anyone know how to do MQSeries MQI calls from a Microsoft SQL Server Stored Procedure or Database Trigger that is written in Transact SQL?
We currently have an application that uses DB2 and SQL Server Tables, IMS Stored Procedures, and a JAVA Routine running on Sybase's Enterprise Application Server (EAS)to move data between SQLServer and IMS databases on OS/390. We'd like to modify this so that MQSeries is used to send the data to and fromIMS, but otherise leave the SQLServer Application Intact.
My problem is that I don't see a way to make MQSeries calls directly from Transact SQL. The SQL 2000 books online imply that you need to create an extended stored procedure to access non-SQL Server routines. This is packaged as a dll that you register with SQL Server. It can then be called from SQL Server as if it were written in Transact SQL.
Microsoft supplies an extended stored procedure called 'xp_cmdshell' that lets you do anything from within SQL Server that you can do from a Windows command prompt. I could code a routine to do the MQSeries calls, passing it the needed parameters and then call it from 'xp_cmdshell'.
Before I do any of this, however, I want to know if anyone knows of a better way. I need to sit down with the customer next week and propose a detailed solution using MQSeries.
Sending Data from IMS to SQL Server is not as difficult because I can set up a trigger queue on the NT side and invoke Microsoft's batch untility (OSQL) as a process to invoke the needed SQL Server code.
We're just getting our feet wet with MQSeries and I'd appreciate all of the help I can get. Thanks.
_________________
Mike Wallis
Hennepin County Information Technology
A1900 Govt Center
300 S 6th St
MPLS, MN 55487
612-348-8748 (voice) 612-348-9595 (Fax)
Mike.Wallis@co.hennepin.mn.us |
|
| Back to top |
|
 |
| kolban |
| Posted: Fri Jul 27, 2001 11:08 am Post subject: |
|
|
Grand Master
Joined: 22 May 2001
Posts: 1072
Location: Fort Worth, TX, USA
|
Mike, I'm not familiar with this language (Transact SQL), perhaps if you could supply a URL reference to the reference material on the web that would really help?
MQSeries has a COM/ActiveX interface to the APIs (see Using COM)
Knowing Microsoft, I would be surprised if their wasn't a COM invocation mechanism as part of this language .... |
|
| Back to top |
|
|
| IM0547 |
| Posted: Thu Aug 02, 2001 10:40 am Post subject: |
|
|
Newbie
Joined: 26 Jul 2001
Posts: 3
|
|
| Back to top |
|
|
| MQSIBM |
| Posted: Thu Nov 01, 2001 6:46 am Post subject: |
|
|
Novice
Joined: 31 Oct 2001
Posts: 18
|
[ This Message was edited by: MQSIBM on 2001-11-02 07:26 ] |
|
| Back to top |
|
|
| jsware |
| Posted: Tue Dec 24, 2002 6:25 am Post subject: |
|
|
Chevalier
Joined: 17 May 2001
Posts: 455
|
The following code will connect to a queue manager called "HALXP" and open the "SYSTEM.DEFAULT.LOCAL.QUEUE".
It uses the sp_OA* stored procedures to get access to the MQAX ActiveX (aka Ole Automation) classes. Read up on this in books online and you'll be able to send messages directly from Transact SQL. It's very long winded though... It helps if you know Visual Basic because the MQAX library help gives VB as an example and you'll have to interpret...
Regards
John.
The Code:
DECLARE @objMQSess int;
DECLARE @objMQQMgr int;
DECLARE @objMQQueue int;
DECLARE @hr int;
DECLARE @src varchar(255), @desc varchar(255);
--Set MQSess = New MQSession
--Set QMgr = MQSess.AccessQueueManager(QUEUE MANAGER NAME)
--Set Queue = QMgr.AccessQueue(QUEUE NAME, MQOO_options)
/*Create MQ objMQSess*/
EXEC @hr = sp_OACreate 'MQAX200.MQSESSION', @objMQSess OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMQSess, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
/* Set the Queue Manager object */
EXEC @hr = sp_OAMethod @objMQSess, 'AccessQueueManager', @objMQQMgr OUT,
'HALXP'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMQSess
RETURN
END
/* Set the Queue object */
EXEC @hr = sp_OAMethod @objMQQMgr, 'AccessQueue', @objMQQueue OUT, 'SYSTEM.DEFAULT.LOCAL.QUEUE',
17 -->MQOO_OUTPUT Or MQOO_INPUT_AS_Q_DEF
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMQQMgr
RETURN
END |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
