I have a 3rd party app that uses SQL Server as a back end. Specifically we have it on SQL Server 2000, SQL Server 2008 and SQL Server 2008R2. Any solution must be available to all, but solutions for only some would still be of interest.

The app is bespoke, out of support and the vendor doesn't exist any more. What I want to do is when it writes an insert is have a trigger to read that insert and start off a complex process. However, the app doesn't return focus until the SQL insert completes which means a lengthy trigger will cause the app to think SQL Server isn't responding and cancel/rollback the transaction.

Is there any kind of trigger that will 'release' immediately and then continue on with its processing? Or perhaps some other solution that I can implement internal to SQL Server.

  • I'm wondering what the use case for this is. I can think of some hacky solutions but the requirement should be clearer
    – Tom V
    Jul 29 '15 at 13:02
  • 5
    You can use service broker or start a job. Jul 29 '15 at 13:04
  • 1
    Hi @TomV. It's to write further information for the bespoke app to display to the user, at a few minutes interval. Doing the same logic in the tools provided by the app lies somewhere between complex and impossible.
    – Paul
    Jul 29 '15 at 13:49
  • I had to step away to research what it was. It certainly sounds the thing and perfect.. it will allow me to add a new string to my bow. First person to post service broker as answer now wins
    – Paul
    Jul 29 '15 at 14:19
  • @TomV I was too hasty. ServiceBroker was introduced in SQL2005 so is not suitable for myself. Jobs as I understand it, running even just 1 per min. creates a lot of tasks in SQL Agent that isn't necessarily clear to a developer ..
    – Paul
    Jul 29 '15 at 14:22

I think I would build a table with a queue of some sorts, and have a job poll that table and pick up any work it has to perform.

So for example your trigger writes a record id in a table called processqueue, then you have a job that picks up all the records from the processqueue table and do it's heavy lifting for each and every record, removing the record from the queue once finished.

Schedule that job to run according to latency requirements

  • I've done this successfully when I didn't have the time to properly implement it in Service Broker.
    – dpw
    Jul 29 '15 at 17:31

Even with SQL 2000 Service broker is still your answer. I'm not going to go into how to set up a service broker solution here because to be honest it's complicated and I've only done it once.

That being said, design your service broker system in 2008 and 2008 R2. For your 2000 system design a second SB system on one of your 2008 R2 systems (let's use the most advanced tool you have). Have a trigger in your 2000 system do a simple insert into a table on your 2008 R2 that controls the SB system for your 2000 data. It can do your complex processing locally then push the data back to the 2000 system. Yes this is kind of hokey but as long as the systems are close together (fast network speed between them) your trigger speed should be quick enough to satisfy your application and the data should get written back quickly enough to satisfy any requirements on that side of things.

Here are some links on service broker to get you started.

  • Thanks Kenneth, it's a good answer. Unfortunately the SQL2000 servers are not at the same physical sites as the servers with later a version on.
    – Paul
    Jul 30 '15 at 7:58
  • Then I would ask 2 questions. How good is the connection between them? Can you manage anyway? Or how much latency can there be before the "complex process" is complete? Could you put the rows into a 2000 table and then have a scheduled job run every hour (or even less) and process the rows in that table? If that's a viable option it's even better because then you can do your process in batch not row by row.. Jul 30 '15 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.