March 8, 2010 at 5:23 pm
A web service reference has been provided as a means to inform an external web application about status changes (data updates) in a database (one or more rows in a table) on the ms sql server. Could for example such a web service be called by a mechanism using a trigger - does anyone have experience or suggestion to share. Thanks in advance.
March 8, 2010 at 8:35 pm
About the only way to directly call a web-service from SQL would be to use SQLCLR, HOWEVER, with that said, you have to import several assemblies into SQL before you can even try.
Also doing the call from a trigger is problemtic, the reason is that the insert/update/delete cannot complete until the trigger returns so your front-end is sitting there waiting.
I think a better methodology would be to use Service Broker and start a "conversation" in the trigger would would run REALLY fast and then use "External Activation" to call the web-service.
I have done a fair amount with service broker but have not used external activation, only internal activation. But from my reading it seems to fit the bill.
CEWII
March 8, 2010 at 8:46 pm
Service Broker to SQL CLR should work OK too. IIRC, reading from a Web Service is one of the first SQL CLR examples in BOL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 8, 2010 at 9:05 pm
Really, IIRC, it required you to load a bunch of assemblies into SQL before you could reference the required assembly due to dependencies. I could be wrong though..
Either way, I still like the service broker way better due to the asynchronous nature.
CEWII
March 8, 2010 at 9:25 pm
I thought that was if you tried to do it with WCF? But you could be right, WebServices through .Net is definitely not a strong area for me. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 8, 2010 at 9:27 pm
Nor me, it has been a while..
CEWII
March 9, 2010 at 1:11 am
Thank you for the replies - I'll check out the suggestions. For your information, let me only clarify that the task in question (for me this time) is to 'send' an id (number) to an external application by calling (or accessing) a web service with the id as a parameter.
March 9, 2010 at 9:50 am
I don't believe your clarification really makes any difference, you still need to have the underlying framework available.
CEWII
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply