SQL Server 2005 stored procedure to read/write to Websphere MQ

I manage a SQL 2005 database and was asked to come up with a way to read/write messages on a MQ server.  I have downloaded the MQ client v7 on my server.  The MQ server is managed by someone else and they have set me up with my designated Queue and Channel.  Can anyone give me some details on what I need to do on the SQL side to set up the connection from my stored procedure to the MQ server?   I read on another post to write a COM object that processes the messages to/from the queue, and then use sp_OACreate to create an instance of that object inside your stored procedure.  Can anyone help me figure this out?   Thank You.
DueFromAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nmcdermaidConnect With a Mentor Commented:
Yes. Your support person is spot on.
All you need to do is add some code to your app which inserts data (whether bya SP or not)... and maybe make your code a service which runs all the time and is triggered by a queue event.
That takes care of transferring data from the queue to the database.
To get data from the database to the queue, you can create a .Net CLR stored procedure, which is a database stored procedure which calls .Net code directly.
You can create a database trigger on a table which calls your stored procedure and in this way data inserted into a table automatically gets added to the queue.
Its a long time since I've done any code and I haven't worked with a queue before but this is how I see it.
0
 
nmcdermaidCommented:
What interfaces/SDK methods does the MQ client give you?
SQL can access COM objects using those SP's. It can also call command lines with xp_cmdshell.
Weneed to know what interfaces the client gives you before we can suggest a method.
0
 
DueFromAuthor Commented:
The supported API's are C, Cobol, PL/I, Java, RPG, C++, JMS, Perl, Windows Powershell, XMS for C/C++ and .Net, .Net, SOAP.  Is that what your asking?   I actually first created a small .net C# application, built a form to test connecting and sending/receiving a message off my queue and that worked.  I did this to just make sure the MQ client on my server was working.   The Queue on the MQ Server can trigger a stored procedure so that my data on the queue can insert directly into my database.  I just have no idea on my end what I need to do.
0
 
DueFromAuthor Commented:
I just talked with my MQ support person and he thought the best approach would be for me to use my .Net C# application to connect and Get data from my Queue and then use this app  to execute a stored procedure to insert this data into my SQL database.   Any suggestions?
0
 
VoloxConnect With a Mentor Commented:
This article may help you out. It shows how to put messages into a MSMQ queue from SQL server.  I know you said you are using MQ series, but functionally it should be similar only refrencing the DLLs for the MS Series client rather than the MSMQ ones.  Like nmcdermaid said, you create a stored proc that calls .Net code that puts the information into the queue.
http://support.microsoft.com/kb/555070

The other option is to use Microsoft SQL Service Broker.  In this scenario you would push a messag into a service broker queue from your stored procedure (which would happen asyncronously).  Then you could use your .Net service to read the queue from service broker and push the messages into MQ Series.  Here is the intro to Service Broker (I'm sure you can find plenty of other info on it if you choose to use it).
http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx

The Service Broker solution might have more latency than directly pushing the messages into the queue from a stored proc, but it has the advantage of avoid an impact to your stored procedure due to queue communication problems.  Depending on what you are trying to do, it may be better to avoid potential connectivity issues having an impact to the ability to complete the rest of the stored procedures work.
0
All Courses

From novice to tech pro — start learning today.