SQL Server Performance

scheduling a webservice with the help of tsql

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Oct 26, 2009.

  1. shankbond New Member

    Hi,
    I have a question here, is there possible to run or schedule a webservice (and passing arguments to it) through tsql?
  2. satya Moderator

    Yes you can with the help of Service Broker, you can create Endpoints, which are ports in the server that listen for WSDL traffic. You can use them to connect two systems or speak directly to an application, and you can schedule and batch operations that return data or even do other work.
    Edit:
    You can use SSIS too in order to call/manage the webservices http://technet.microsoft.com/en-us/library/cc917721.aspx fyi
  3. shankbond New Member

    Hi Satya,
    [quote user="satya"]
    Yes you can with the help of Service Broker, you can create Endpoints, which are ports in the server that listen for WSDL traffic. You can use them to connect two systems or speak directly to an application, and you can schedule and batch operations that return data or even do other work.
    Edit:
    You can use SSIS too in order to call/manage the webservices http://technet.microsoft.com/en-us/library/cc917721.aspx fyi
    [/quote]
    Thanks for the reply, but can You provide some sample code for invoking a webservice through T-sql?
    while searching on google I found this code:
    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);
    Code Snippet
    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get',
    'http://www.webservicex.com/stockquote.asmx/GetQuote symbol=MSFT', --Your Web Service Url (invoked)
    'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
    Select @ResponseText
    Exec sp_OADestroy @Object

    I suppose this is the correct code to invoke a webservice from a t-sql, and it wont cause any performance issues on the sql server itself(sorry don't know much about invoking webservice, though I read the link and it helped me to leverage my knowledge but I am still a beginner[:)] )
    Any help shall be appreciated

Share This Page