Best Way to call a Web Service from TSQL

What is the best way开发者_如何学C 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

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.

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.

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 willl be able to handle the latency without tying up the database.

As mentioned in @Keith's answer making a full call to a web service could be very bad. Imagine the web service is down and you have a lot of updates locked and waiting to access your data. You can use SQL Service Broker though, here is a good tutorial on using them with triggers. Here is their sample trigger code after the queue has been setup. You can always create a service that watches the queue and actually calls a web service if you want.

--Create trigger for update
CREATE  TRIGGER dbo.Trg_DepartmentMaster_Update 
    ON  dbo.DepartmentMaster FOR UPDATE 
AS BEGIN
    SET NOCOUNT ON;
    DECLARE @MessageBody XML  
    DECLARE @TableId int 

    --get relevant information from inserted/deleted and convert to xml message  
    SET @MessageBody = (SELECT DepartmentId,Name,Description FROM inserted FOR XML AUTO)

    If (@MessageBody IS NOT NULL)  
    BEGIN 
        DECLARE @Handle UNIQUEIDENTIFIER;   
        BEGIN DIALOG CONVERSATION @Handle   
        FROM SERVICE [TestServiceInitiator]   
        TO SERVICE 'TestServiceTarget'   
        ON CONTRACT [TestContract]   
        WITH ENCRYPTION = OFF;   
        SEND ON CONVERSATION @Handle   
        MESSAGE TYPE [TestMessage](@MessageBody);
    END
END