SQL Server Performance

Webservices and trigger

Discussion in 'SQL Server 2005 General Developer Questions' started by Shyrill, Aug 17, 2009.

  1. Shyrill New Member

    Calling webservices in a trigger is safe?or may lead to a performance issue?
    please be advise!
  2. MohammedU New Member

    Welcome to the forum...
    I don't think it is good idea and it may cause issues...
  3. Shyrill New Member

    Really?how do you say so?
    Thanks for the welcome!
  4. Adriaan New Member

    SQL Server can only finish the action that fired the trigger, after the trigger code has finished executing. The calling application will often be waiting for confirmation from the server that the action has finished.
    If your trigger calls a web service, and for instance the web service is unavailable, the whole action may remain suspended, or end with an unexpected result, which may cause the action to fail, or cause the calling application to hang.
    One way around this is to make the trigger add a row to a log table, and to have a job scheduled to check for entries in the log table at a given interval. This job can then call the web service, without risk of interrupting the inserts/updates/deletes on the table.
  5. Shyrill New Member

    mmmmmm so you mean that the risk will only occurred onced the web service is unavailable?but if the web services is always available it will lessen the risk? correct or not?
    "One way around this is to make the trigger add a row to a log table,and to have a job scheduled to check for entries in the log table at agiven interval. This job can then call the web service, without risk ofinterrupting the inserts/updates/deletes on the table." in this case, i will first transfer the data in a log table then from a log table it will call the webservice then this is the only time that i will insert the data to the another database?am i understanding is right? if yes, it will lessen the risk of performance issue even the webservices will be unavailable for a certain period of time?
    Thanks

  6. FrankKalis Moderator

    [quote user="Shyrill"]
    in this case, i will first transfer the data in a log table then from a log table it will call the webservice then this is the only time that i will insert the data to the another database?am i understanding is right? if yes, it will lessen the risk of performance issue even the webservices will be unavailable for a certain period of time?
    [/quote]
    It won't lessen the risk of performance issues. It will ensure that your normal operations continue to work by running asnychronously when you're not using a trigger.
    Another point: Have you ever thought of using Service Broker?
  7. Shyrill New Member

    mmm it seems that it is really not a good idea to call webservices in a trigger :( Service Broker sounds new in my ears....:) but i will look on it...Perhaps you can give me some idea with that :)
  8. Shyrill New Member

    oooops... I think I can't used We Broker heheheh why?because it is an application that we need to purchase :(
  9. FrankKalis Moderator

    [quote user="Shyrill"]
    oooops... I think I can't used We Broker heheheh why?because it is an application that we need to purchase :(
    [/quote]
    So, this is a third-party application you're talking about? Service Broker is an integral part of SQL Server. Not sure if it is only available in Enterprise Edition though. Don't think so, but am not sure.
  10. Shyrill New Member

    Yup Actually I am developing a third party to integrated the database of n accounting application and an online application :) as of the moment I am using a trigger but the main concern in using the trigger is the performance issue especially in a Linked Server and in RDP set up of Server.
    Thanks
  11. Adriaan New Member

    Don't call outside processes from triggers.
    Please.[:'(]
  12. Shyrill New Member

    Hi Adrian,
    I will follow you, I will not call outside processes from trigger as what you have suggested.[;)]
    Thank you guys,
    * I will find another solutions to my problem better than using web services.
  13. satya Moderator

    If you are good at programming, you can take help of SMO in SQL Server.
  14. FrankKalis Moderator

    [quote user="satya"]
    If you are good at programming, you can take help of SMO in SQL Server.
    [/quote]
    Sorry, but I'm not sure I understand. How can SMO be of help here?
  15. satya Moderator

    I believe if you can explain further about your requirement we can offer a better solution, rather than trial and error setup, as referred by Adrian it is not a best practice.
    You can take help of SQLCLR in this case to accomplish the task.

Share This Page