| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Putting result of sql query on a que |
« View previous topic :: View next topic » |
| Author |
Message
|
| luisdev |
 Posted: Thu Jun 16, 2011 6:23 am Post subject: Putting result of sql query on a que |
 |
|
Newbie
Joined: 16 Jun 2011
Posts: 2
|
I have a MS SQL Server 2005 stored procedure that queries a table in the database and returns a record. I want to put the results of that query onto a MQ queue.
My procedure is:
CREATE PROCEDURE GetUser
@user nvarchar(50)
AS
SET NOCOUNT ON;
SELECT Username, FirstName, LastName
FROM Users
WHERE Username = @user;
GO
I call this procedure using:
EXEC GetUser @user = 'abc123'
and it returns this record:
Username, FirstName, LastName
abc123, John, Brown
I want to put that record (abc123, John, Brown) onto a MQ queue so that it can be consumed by another system.
How do I connect to the MQ queue from the stored procedure? Are there any articles that explain how to integrate SQL Server stored procedures and MQ queues?
Thanks. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Jun 16, 2011 6:35 am Post subject: Re: Putting result of sql query on a que |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| luisdev wrote: |
| How do I connect to the MQ queue from the stored procedure? |
Carefully and with difficulty I'd imagine! 
(Before anybody else says it, I'll pedantically point out you don't connect to a queue, you connect to a queue manager & write to a queue)
3 possible methods occur to me:
- wrap the EXEC in some kind of process that pipes the result into the queue
- use whatever JMS support SQL Server has (if any) to write the message
- create a user defined function in VB / C# via the CLR that can write to a queue
Other, better methods are undoubtably available and there maybe really good reasons why those methods won't work!
Do let us know how you get on.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 16, 2011 6:35 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Well, if you were actually using Message Broker, which your choice of putting this post into Message Broker Support suggests, then you would write ESQL to call the stored procedure and return the data, and then construct a logical message tree to represent the data.
Then you would pass the logical message tree to an MQOutput node that would serialize the logical message tree into a physical message and write it to a queue.
If you are not using Message Broker at all, and are merely asking how to access MQ from within an SQL Stored Procedure, you should mention this so the moderators can move this thread to a different forum.
in general, to access MQ from within any kind of database stored procedure, your database needs to know how to access the MQ APIs. You could probably write a set of functions in Visual C++ or etc. to make this available from SQLServer, if it is not already. But that's complicated and tricky.
There may also be existing functions that can be called - or you could write the stored procedure in .Net perhaps and call the MQ APIs for .NET. But I'm not personally aware of such. I'm sure Mr. Google has more information. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jun 16, 2011 6:38 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Moved to MQ forum until someone mentions broker.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| luisdev |
| Posted: Thu Jun 16, 2011 12:39 pm Post subject: |
|
|
Newbie
Joined: 16 Jun 2011
Posts: 2
|
Apologies for posting in the wrong group. I wasn't sure what the right group was...
I found this thread in the forum. It mentions using the MQAX200 ActiveX API to connect to the queue manager. It is quite an old discussion so is that still an acceptable way to connect to the queue manager, or are there now better ways? Is MQAX200 still usable today, or has it been replaced by something else? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 16, 2011 1:02 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The MQAX200 Active X API has been replaced by the .NET interfaces.
I don't know what support SQLServer has for calling .NET code.
The typical way that one handles "Read a table, and put the contents into a queue" is with an application that performs a call to the database, and a call to MQ, that is not a Stored procedure.
In general, I would only code a stored procedure that talked to MQ if the MQ action was fundamentally a side-effect of the procedure (like send a log message or a change message) rather than the desired result.
If the MQ action was the desired result, I would code that in the application and not in a stored procedure. |
|
| 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
|
|
|
|
