Hello, What is the best way to call a Web Service from TSQL? I would Like to write some triggers that call out to a web service. Is there a generally used best practice for this?
Implementations would need to be handled in SQL Server 2005 and 2008
+5
A:
I know you can embed .NET CLR code in SQL Server, but whether CLR code can be called from a trigger, I don't know. I'd think about a different architecture of this by having one table that will get records inserted any time you need to call the web service, and have an external app running to poll the table, and call the web service from there. You're going to have alot of trouble debugging, error logging, etc. if you try to call web services directly inside a trigger. Just my 2 cents.
Jeremy
2010-04-21 13:33:32
+1, I don't think throwing a call to a Web Service in the middle of a SQL transaction is a good idea. Every trigger runs in an automatic transaction with the firing statement. You will hold locks for this entire transaction, plus more time if you have your own transaction around the firing command and other commands. Triggers need to run very fast or your system will slow down.
KM
2010-04-21 13:38:27
+6
A:
From everything I've ever heard, it is no kind of "best practice" to make website calls from anywhere "within" the database, and making a web call from within a trigger makes me flinch [latency, delays, blocking, deadlocks... oog]. Databases make great back-ends, but very poor front-ends. You're probably much, much better off having a dedicated application do your web-and-database coordination.
Wiring something into a SQL Agent call might work, but note that there is no "native" way to call a website from within a job step. They probably designed it that way on purpose.
Philip Kelley
2010-04-21 13:37:31
+1
A:
As already mentioned here, making a call to a web service from a trigger will kill your database performance due to the time it would take to make the call.
SQL Server does have a feature called SQL Server Broker which is designed for integrating asynchronous messaging in to SQL Server. While I have now experience of using this myself it may be a good starting point if you wish to keep all the processing logic within SQL Server.
If you can though I would suggest moving the responsibility for calling the service to whatever application is using the database. At this level you'll have be able to handle the latency without tying up the database.
Keith Bloom
2010-04-22 13:03:13