Fire a Stored Procedure from MSMQ Trigger Rule

[guest2013-1]

I've been playing around with MSMQ again after several years have passed since I've messed around with it and noticed you could setup a trigger within MSMQ with a rule to say "if x message received fire this COM or Executeable and pass these parameters"

Now I was wondering, is there an easy way to fire a stored procedure using the MSMQ triggers?

I'm thinking you could do a command line executable that takes the parameters you send, the message body will contain a "stored procedure" to fire (or I could use the label for it) and the executable can then dynamically process the xml message body and send those are parameters to the stored procedure (specified in the message label)

I'm wondering if that would be too complicated? (I can do it, have done it before, I'm thinking about processes)

For example I want System 1 to "talk to" System 2

System 1 would trigger a message to go on the queue, System 2 would read it and then process accordingly

If I want System 1 to send a NEW type of message that will be processed with a new stored procedure, it's a question of generating the XML on System 1, and writing the procedure on System 2?

I'v already done a couple of projects in "extended stored procedure" (CLR) where I can read from (and output in a resultset/table) and send messages with the queue

by using a table declaration I have something like

declare @t as table (MessageLabel,MessageBody)

insert into @t
exec dbo.msmqReceive 'queue to receive from'

select * from @t where "whatever I want"

or I was thinking then I can send the @t to another stored procedure for processing

But then I'd have to call stored procedure x to read messages that will call stored procedure z to process and have to modify x to account for any new stored procedures that does the processing/talking between systems

Your insights would prove very helpful

With the first mentioned scenario, I'd have MSMQ Trigger call a generic command line exe that will process it automatically, so each new message on System 1 would just look for and trigger a stored procedure on System 2. One caveat that I might run into is that the command line executable might not necessarily be as dynamic considering System 2's connection string, but then again I could perhaps send through (with the message, which might be a PIA for new devs taking over the scripting of new integrations) the connection string each time?

Hmmmmmm

which is the best way?

I might want System 1 to talk to System 3 too and I don't want to go modify code to do it

 

[guest2013-1]

The MSMQ Trigger and CLR send works really nice.

When you put a message on the MSMQ it adds the instance/catalog that originated the message for you so you can build the connection string dynamically without worrying about other devs remembering to add it to their XML outputs. Going to play around with the command line executable now to process the xml and do some stress tests