Home Forums Programming CLR Integration and Programming. Best practices for event driven processing when data inserted into table RE: Best practices for event driven processing when data inserted into table

  • December 3, 2014 at 4:19 pm

    #1762533

    cppprogrammer (12/3/2014)

    Yes sorry I should have clarified. The initial execution of the external process via the trigger was to be a "fire once" operation that returned immediately and designed to ignore whether that external process execution was successful or not. That would maintain the timeliness and speed of the trigger operation and allow it to commit quickly.

    The "retry" mechanism was not going to be done within the trigger/CLR proc at all. We have a Windows Service ("scheduler service") that can perform this role on a schedule. That way, in ideal scenario, the CLR proc does the job. If the external process fails for whatever reason, the scheduler service will mop up later.

    That sounds reasonable. Just be aware of the risks of sticking an external call into a trigger. Don't get me wrong, I am all for doing cool and useful stuff like this. In fact, I sell a library of SQLCLR functions and procs, SQL#[/url], and one of the functions is a generic HttpRequest that is used pretty heavily by some customers. I just don't think anyone is using it in a trigger ;-).

    So the main things to watch out for are:

  • The external call, I believe, will not yield its thread and hence will tie up one of the schedulers. This is another concern regarding potentially long-running calls. Even if the call normally takes < 30 ms, if the connection timeout is 60 seconds and there is a connection problem, you are tying up an entire thread for that time, not just the transaction. So maybe set any timeouts to relatively low values.
  • Only so many network handles can be open and it is not many. Hence you need to be very diligent about closing all WebRequests, Streams, etc. It is very important to ensure this happens in finally blocks, etc such that all exceptions will always close the connections. While I have not experienced this myself, I have heard from a few folks that when doing network calls, they can make a few (literally, as in: 3) and then they start getting errors that can only be resolved by restarting the SQL Server service.

    • If you have those 2 areas covered then you should be ok.

    Take care,

    Solomon..

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR