Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
grahamed
Starting Member
3 Posts |
 Posted - 2005-06-11 : 14:12:18
|
| I have created a trigger which uses xp_cmdshell to call a vb program which can take a long time to complete. Unfortunately the trigger does not complete until the called program has finished processing.I have tried calling progA which in turn uses shellexecute to call progB(my lengthy app). ProgA actually seems to finish but the trigger will still not complete until ProgB has finished processing.Basically I want to call a program from SQL when an event occurs like a record update, and then have both SQL and the called program carry on running asynchronously without further interaction.This must be a common requirement. Can anybody provide a solution to this??? Source code would be great. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-06-11 : 14:33:02
|
| You really should design a queue table. The trigger can add an entry to the table. You can then poll the table and execute multiple streams of your VB app based on what's in the table at any given time. It allows you to meet your requirements. You can also track historical times so you know how long it's taking each time and can track completion, etc.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
grahamed
Starting Member
3 Posts |
Posted - 2005-06-11 : 15:14:33
|
| I have just read that sp_Start_Job might do the trick. The trigger will start the job which runs my program and apparently the trigger will then terminate as soon as the job has started. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-06-11 : 15:20:06
|
| It's the wrong approach. You shouldn't be starting applications from inside SQL Server. That's not what it's for. Think about what I said and why you might want to do it that way.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
grahamed
Starting Member
3 Posts |
Posted - 2005-06-11 : 16:47:16
|
| yes I see what you mean. You are right. Thanks for your advice.The job approach wouldn't have worked anyway because you can't execute multiple instances of the job, which would have been required. |
|
|
|
|
|
|
trigger using xp_cmdshell to timeconsuming app