| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2003-12-02 : 12:57:47
|
| Hey all,Does anyone have a good solution for executing an EXE when a row is inserted into a table? I've use a trigger with xp_cmdshell in it before, and all was well till the EXE had an issue. Once that happened, there was some blocking / locking issue that was really nasty to deal with. Is there a better / safer / more reliable way?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-02 : 13:02:47
|
| Maybe in your trigger, you could start a job that executes the EXE. You would use sp_start_job for this. This happens very fast regardless of the status of the job.Tara |
 |
|
|
Granick
Starting Member
46 Posts |
Posted - 2003-12-02 : 14:08:18
|
| I would have to second the idea of using a job to fire the EXE, especially in a trigger. I learned my lesson about using xp_cmdshell to run stuff a while ago, in procs and such, and try to avoid that if I don't have to, for the reasons mentioned above. If everything is working fine, you are golden, but when something happens, you go downhill quickly.Shannon |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-02 : 19:52:06
|
| What happens if I get a lot of hits to this trigger very quickly? Will each job have to wait for the previous one to "finish"?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-02 : 19:53:53
|
| If the job hasn't completed yet, then the sp_start_job has no effect. Is that what you want?Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-02 : 19:58:38
|
| I'm not sure if that's the way I need it to work or not. I think that will work ok for me. I just have to code knowing that I may or may not kick off this EXE for each row that is inserted into this table. Basically, instead of this EXE telling another EXE to grab a single row, it needs to grab any rows that need to be processed. When it's done, it should check one more time to see if there's anything left that it might have missed somehow, and then terminate and wait for the next message from this job. I think my English teacher would shoot me if she saw that last sentance. :)Thanks for the info everyone! Sub 2 minute response time from Tara, nice.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-02 : 20:01:41
|
| Can't you just schedule the EXE to occur every 5 minutes or so and have it process all of the rows each time? Doing it that way would eliminate the need for a trigger. We've got a similar situation but we have it scheduled to run every minute.Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-02 : 20:17:22
|
| I've been down that road as well. The application that I'm developing is a report processing system for a website. For the most part 99% of the day the "processor" will be doing nothing. So, all of those calls to the database would be basically wasted. I've got some apps that polls the database at intervals now, and they work ok. They work ok until there is a tons of data in that table and figuring out if there's anything to do becomes an expensive operation. That's why I was trying to head down the "event driven" way to make this work. I want my "Processor" to do as little as possible. The other reason for this "event driven" paradigm is that I need to process these reports as fast as possible. Users (esp. Web Users) hate to wait, especially on what they feel is a simple report. Given all of that info, what would you do Tara? Poll every X minutes or try ot make the event driven way work?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-12-02 : 20:20:52
|
| Michael,Does this insertion happen via the middle tier? If so, why not do it there?DavidM"SQL-3 is an abomination.." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-02 : 20:21:28
|
| Well, one other way that was done here is that the EXE was made into a service that periodically wakes up to see if there is anything that it needs to do. So the EXE is always running like any other service. The service that we have runs on the web server.If the EXE has nothing to do, is it able to close quickly? Do you really need this processor though? Can't it be built into the application?Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-02 : 20:31:28
|
| What I'm thinking is that the Processor will be a Service that sits and waits to be told to do something. When there's a new report to process, a record is written into the ReportQueue table. At this point, a trigger is fired kicking off the Job. The Job calls a simple "Waker" EXE that will send a message (via TCP/IP) to a target Processor (there could be many processors one many PCs, just in case one dies). The message is basically "Wake up, there's something for you to do now" and then the Waker terminates. The Processor then looks in the ReportQueue and gets any reports that need to be processed. It processes them, and updates the status of the record in the ReportQueue table. During this whole process there's a web page that periodically looks at the status of that user's ReportQueue records and lets them know if they are done yet and maybe how much longer until they are done.To me, this smells like a Rube GOldberg solution, but I'm not sure of a better way to achive the "instant" processing without pounding the database by one or more servers running my Processor service.I just read DavidM's post and he has an excellent thought there. Why didn't I think of that! I'm not 100% sure if that will work due to our network configuration, but I'm gonna give that a try. It's going to cut out a lot of work and complexity. Thanks for your thoughts on this Tara and David!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
Execution of external EXE