| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| MQ Access FRom SQL Server Stored Procedure |
« View previous topic :: View next topic » |
| Author |
Message
|
| MillsPerry |
 Posted: Thu Jul 07, 2005 7:44 am Post subject: MQ Access FRom SQL Server Stored Procedure |
 |
|
Acolyte
Joined: 08 Oct 2001
Posts: 59
Location: ZyQuest, inc.
|
Problem: We are trying to put messages on a queue from a stored procedure (MS SQL Server 2000) using the ActiveX API (MQAX200). The procedure works when I run it from an MSDE database on my workstation, but when we run it from the database server we fail with a reason code of 6000 (MQRC_LIBRARY_LOAD_ERROR).
It manages to create the MQAX200.MQSession object, but fails to connect to the queue manager.
I have trouble believing it really is a library load error because we tested a VBScript that called the same ActiveX components on that server and it ran fine. We have also tested with utilities amqsgetc and amqsputc.
Here is the SQL for the stored procedure. Again, it runs on MSDE, but not SQL Server 2000:
| Code: |
USE MqTest
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE cmms_mqput_sp
@i_msg varchar (8000),
@i_qmgr varchar (30),
@i_qname varchar (30)
AS
BEGIN
DECLARE
@objSession int,
@objQMgr int,
@objQueue int,
@objMsg int,
@objPMO int,
@rc int,
@ErrSrc varchar(255),
@ErrDesc varchar(255),
@TestID int,
@Desc varchar(255),
@MsgXml varchar(255),
@source varchar(255),
@description varchar(255),
@output varchar(255)
--************************************************************
-- CREATE THE MQSession OBJECT.
--************************************************************
EXEC @rc = sp_OACreate 'MQAX200.MQSession', @objSession OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objSession, @source OUT, @description OUT
IF @rc = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
--************************************************************
-- CONNECT TO THE Q MANAGER.
--************************************************************
EXEC @rc = sp_OAMethod @objSession, 'AccessQueueManager', @objQMgr OUT, 'QMORAMQD1'
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objQMgr, @source OUT, @description OUT
IF @rc = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed for AccessQueueManager.'
RETURN
END
END
--************************************************************
-- OPEN THE Q FOR OUTPUT.
--************************************************************
EXEC @rc = sp_OAMethod @objQMgr, 'AccessQueue', @objQueue OUT, 'CMMS.CMMS_REQ_OUT_CATCH', 16
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objQueue, @rc
RETURN
END
--************************************************************
-- CREATE THE MESSAGE OBJECT.
--************************************************************
EXEC @rc = sp_OACreate 'MQAX200.MQMessage', @objMsg OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
--************************************************************
-- CREATE THE PMO OBJECT.
--************************************************************
EXEC @rc = sp_OACreate 'MQAX200.MQPutMessageOptions', @objPMO OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objPMO, @rc
RETURN
END
--***********************************************************
-- SET THE PMO OPTIONS (MQPMO_NEW_MESSAGE_ID)
--***********************************************************
EXEC @rc = sp_OASetProperty @objPMO, 'Options', 64
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objPMO, @rc
RETURN
END
--************************************************************
-- PUT TEXT IN THE MESSAGE.
--************************************************************
EXEC @rc = sp_OAMethod @objMsg, 'WriteString', NULL, 'TEST Message FROM SQL Server'
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
--************************************************************
-- PUT THE MESSAGE ON THE Q.
--************************************************************
EXEC @rc = sp_OAMethod @objQueue, 'Put', NULL, @objMsg, @objPMO
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
END
SET QUOTED_IDENTIFIER OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Jul 07, 2005 7:49 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is it possible that the SQL Server service user does not have read permissions for the MQ install bin directory?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| MillsPerry |
| Posted: Thu Jul 07, 2005 8:00 am Post subject: |
|
|
Acolyte
Joined: 08 Oct 2001
Posts: 59
Location: ZyQuest, inc.
|
| I have verified SQL Server Service user rights and SQL Server Service user has administartive rights on the box. |
|
| Back to top |
|
|
| sjensen |
| Posted: Fri Jul 08, 2005 1:01 am Post subject: |
|
|
Centurion
Joined: 18 Dec 2003
Posts: 134
Location: London
|
Hi
Are the WMQ library directory in the library path?
Cheers
Stefan |
|
| Back to top |
|
|
| MillsPerry |
| Posted: Fri Jul 08, 2005 5:03 am Post subject: |
|
|
Acolyte
Joined: 08 Oct 2001
Posts: 59
Location: ZyQuest, inc.
|
Hi Stefan,
Yes, it is on the path.
We did a standard MQ client install and rebooted the server for good measure. Also, the same MQAX200 classes work fine when tested from a VBScript. The problem only seems to occur when we try to access MQ from a SQL Server stored procedure.
Thanks!
Mills Perry |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Jul 08, 2005 5:25 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| MillsPerry wrote: |
| Yes, it is on the path. |
I want to make sure you understood Stefan's question.
He asked if the WMQ library folder was on the LIBRARY path for the SQL Server instance.
This is different than the PATH.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| MillsPerry |
| Posted: Fri Jul 08, 2005 7:32 am Post subject: |
|
|
Acolyte
Joined: 08 Oct 2001
Posts: 59
Location: ZyQuest, inc.
|
Jeff,
We are uncertain where to look for this. Are you refering to an environment variable, a registry setting, or what?
Also, since the MQ API we are using is a properly registered ActiveX object, why would SQL Server need the path?
Thanks! |
|
| Back to top |
|
|
| sam05 |
| Posted: Mon Jul 11, 2005 5:38 am Post subject: access MQ from a SQL Server stored procedure |
|
|
Novice
Joined: 11 Jul 2005
Posts: 18
|
Hi
I desperately need a solution for this problem. I am also having the same problem.
MillsPerry, if you have a solution can you please share it with us.
Or can somebody please help.
Thanks |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Jul 11, 2005 6:09 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I don't have a good answer to this, as I've never done it.
MQRC_LIBRARY_LOAD_ERROR says to make sure that "the directories containing the WebSphere MQ libraries are on the PATH".
Putting <MQ install dir>\bin on the system PATH, and putting <MQ install dir>\Tools\lib on the system level environment variable "lib" may help.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| sam05 |
| Posted: Tue Jul 12, 2005 1:42 am Post subject: access MQ from sql stored procedure |
|
|
Novice
Joined: 11 Jul 2005
Posts: 18
|
Hi
Thanks for the reply.
The paths are all right.
But it still wont work.
I want to be able to use the MQCONN,MQPUT,MQGET from SQL 2000 Stored Procedure and put a message on a Queue. Initially my stored procedure was putting a message on SQL table and later to be taken and processed by another system on remote server.
I want to change the system now to put a message on a Queue so that it gets transported to another system via a channel. I do not want to change my VB code to do that, I want a stored procedure to do it.
Can Someone please help. |
|
| Back to top |
|
|
| sam05 |
| Posted: Tue Jul 12, 2005 1:44 am Post subject: access MQ from sql stored procedure |
|
|
Novice
Joined: 11 Jul 2005
Posts: 18
|
Hi
Thanks for the reply.
The paths are all right.
But it still wont work.
I want to be able to use the MQCONN,MQPUT,MQGET from SQL 2000 Stored Procedure and put a message on a Queue. Initially my stored procedure was putting a message on SQL table and later to be taken and processed by another system on remote server.
I want to change the system now to put a message on a Queue so that it gets transported to another system via a channel. I do not want to change my VB code to do that, I want a stored procedure to do it.
Can Someone please help. |
|
| Back to top |
|
|
| mvskumar |
| Posted: Fri Jul 15, 2005 1:16 pm Post subject: Reboot or Restart SQL Server Service after MQ Series Client. |
|
|
Newbie
Joined: 15 Jul 2005
Posts: 1
|
SQL Server Service will not find MQ Libraries until you Reboot or Restart SQL Server Service. It worked for me. Let me know if it does not work for you. Also make sure sql service user account has rights to the MQ library folder.
Thanks
Satish |
|
| Back to top |
|
|
| sam05 |
| Posted: Mon Sep 05, 2005 5:58 am Post subject: |
|
|
Novice
Joined: 11 Jul 2005
Posts: 18
|
Hi
I can put a message on local Queue where Mq server is installed using the code that I found on the net like so using a stored procedure
EXEC @rc = sp_OACreate 'MQAX200.MQSession', @objSession OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objSession, @source OUT, @description OUT
IF @rc = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
--************************************************************
-- CONNECT TO THE Q MANAGER.
--************************************************************
EXEC @rc = sp_OAMethod @objSession, 'AccessQueueManager', @objQMgr OUT, 'QMORAMQD1'
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objQMgr, @source OUT, @description OUT
IF @rc = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed for AccessQueueManager.'
RETURN
END
END
--************************************************************
-- OPEN THE Q FOR OUTPUT.
--************************************************************
EXEC @rc = sp_OAMethod @objQMgr, 'AccessQueue', @objQueue OUT, 'CMMS.CMMS_REQ_OUT_CATCH', 16
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objQueue, @rc
RETURN
END
--************************************************************
-- CREATE THE MESSAGE OBJECT.
--************************************************************
EXEC @rc = sp_OACreate 'MQAX200.MQMessage', @objMsg OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
--************************************************************
-- CREATE THE PMO OBJECT.
--************************************************************
EXEC @rc = sp_OACreate 'MQAX200.MQPutMessageOptions', @objPMO OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objPMO, @rc
RETURN
END
--***********************************************************
-- SET THE PMO OPTIONS (MQPMO_NEW_MESSAGE_ID)
--***********************************************************
EXEC @rc = sp_OASetProperty @objPMO, 'Options', 64
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objPMO, @rc
RETURN
END
--************************************************************
-- PUT TEXT IN THE MESSAGE.
--************************************************************
EXEC @rc = sp_OAMethod @objMsg, 'WriteString', NULL, 'TEST Message FROM SQL Server'
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
--************************************************************
-- PUT THE MESSAGE ON THE Q.
--************************************************************
EXEC @rc = sp_OAMethod @objQueue, 'Put', NULL, @objMsg, @objPMO
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
Now, I want to put a message on a queue defined on a remote machine.
I am running the stored procedure above on machine on which MQClient and SQL server are installed. How do I connect to this remote Qmanager and put a message on its queue?
Your help much appreciated. |
|
| Back to top |
|
|
| sam05 |
| Posted: Mon Sep 05, 2005 6:02 am Post subject: |
|
|
Novice
Joined: 11 Jul 2005
Posts: 18
|
Hi
I can put a message on local Queue where Mq server is installed using the code that I found on the net like so using a stored procedure
EXEC @rc = sp_OACreate 'MQAX200.MQSession', @objSession OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objSession, @source OUT, @description OUT
IF @rc = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
--************************************************************
-- CONNECT TO THE Q MANAGER.
--************************************************************
EXEC @rc = sp_OAMethod @objSession, 'AccessQueueManager', @objQMgr OUT, 'QMORAMQD1'
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objQMgr, @source OUT, @description OUT
IF @rc = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed for AccessQueueManager.'
RETURN
END
END
--************************************************************
-- OPEN THE Q FOR OUTPUT.
--************************************************************
EXEC @rc = sp_OAMethod @objQMgr, 'AccessQueue', @objQueue OUT, 'CMMS.CMMS_REQ_OUT_CATCH', 16
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objQueue, @rc
RETURN
END
--************************************************************
-- CREATE THE MESSAGE OBJECT.
--************************************************************
EXEC @rc = sp_OACreate 'MQAX200.MQMessage', @objMsg OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
--************************************************************
-- CREATE THE PMO OBJECT.
--************************************************************
EXEC @rc = sp_OACreate 'MQAX200.MQPutMessageOptions', @objPMO OUT, 1
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objPMO, @rc
RETURN
END
--***********************************************************
-- SET THE PMO OPTIONS (MQPMO_NEW_MESSAGE_ID)
--***********************************************************
EXEC @rc = sp_OASetProperty @objPMO, 'Options', 64
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objPMO, @rc
RETURN
END
--************************************************************
-- PUT TEXT IN THE MESSAGE.
--************************************************************
EXEC @rc = sp_OAMethod @objMsg, 'WriteString', NULL, 'TEST Message FROM SQL Server'
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
--************************************************************
-- PUT THE MESSAGE ON THE Q.
--************************************************************
EXEC @rc = sp_OAMethod @objQueue, 'Put', NULL, @objMsg, @objPMO
PRINT @rc
IF @rc <> 0
BEGIN
EXEC sp_OAGetErrorInfo @objMsg, @rc
RETURN
END
Now, I want to put a message on a queue defined on a remote machine.
I am running the stored procedure above on machine on which MQClient and SQL server are installed. How do I connect to this remote Qmanager and put a message on its queue?
Your help much appreciated. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Sep 05, 2005 6:19 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
The MQServer environment variable or the Channel table must be in SQL Server scope when SQL Server is started. Or you have to set it before running the MQ commands. And it needs to be in process scope.
Read up on client manuals.
Enjoy  |
|
| Back to top |
|
|
|
|
|
  |
Goto page 1, 2 Next |
Page 1 of 2 |
|
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
|
|
|
|
