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 |
|
JoshSquires
Starting Member
2 Posts |
 Posted - 2007-06-05 : 15:58:13
|
| First forgive me if this is not the right place to post this, I looked around and it seemed like the only logical place. My situation is thisI have an update trigger that checks a field change, if this field change occurred then a xp_cmdshell calls a c# console program I wrote that runs hundreds of different matching scripts and procedures to determine if the changed record matches certain criteria. If it does then an email is sent to the matched records contact.If I run the trigger and just create a file with the changed record ID it works fine.If I just run the C# console program from the command prompt it executes fast and effectively sends out all the matched record emails.However, if I call the C# program using xp_cmdshell from the trigger it takes forever to finish if not eventually timing out.I can use SQL profiler and watch the scripts run from both running the C# program from a prompt and then watch it run from the trigger the same things occur just super super slow from the trigger.Any help would be appreciated, before I just total revise how things occur.Thanks in Advanced.-Josh |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-05 : 16:35:29
|
just as an aside, do you realize that launching a long running process from within a trigger will completely kill the performance of your application?
elsasoft.org |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-05 : 17:04:29
|
| Have the trigger add a row to a table with the changed value.Have a scheduled job poll the table and call the c# app to processes it.Two things you shouldn't do from a trigger - use xp_cmdshell, send emails.Does your c# app ever actually complete when called from the trigger?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
JoshSquires
Starting Member
2 Posts |
Posted - 2007-06-06 : 08:30:50
|
| Thanks for the input everyone!jezemine: I was hoping that with the use of my optimized c# program rather than running all the matching with cursors within the trigger itself would actually speed up this process. But I see now that this is not the case.nr: Great suggestion, I have read other similar suggestions when initially researching my latency issue.Thanks for the help!-Josh |
|
|
|
|
|
|
triggered xp_cmdshell