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 |
|
dotolee
Starting Member
9 Posts |
 Posted - 2010-05-21 : 18:51:32
|
| I'll start of with a little overview of what i'm trying to do. then i'll address the why i have to do it this way. (pootle flump, if you're reading this, this first part will be a bit of review a for you! it's a different type of question... same fundamental issue)i need a table trigger that will write the pk value to a temp table whenever a record has been modified to have a status of 1. this temp value will then be read by a stored procedure which will simply return the pk value it reads. it will also delete the value from the temp table, ensuring there's only ever one value at a time in this table. this stored proc will be called by an external application...as for why i'm doing it this way. we are using an iway / biztalk type application for the industrial world and as per their support department, this is the only way to get data out from a sql database into their app without "knowing" which record to request. So in a nut shell we have: 1. sql database table changes. 2. trigger runs and save changes to another temp table within the same database. trigger then shells out via xp_cmdshell and runs a dos utility supplied by this third party company. this tool essentially runs their program, which in turn calls the second stored proc within the original sql database.3. this second stored proc is what queries the temp table, grabs the value and returns it as an output parm. it then deletes the value.When i trigger the trigger by making a change on my database table, i get the following error after a few seconds: no row was updated. the data in row X was not committed. error source: .net SQLClient Data ProviderError message; timeout expired. The timeout period elapsed prior to completion or the server is not responding.my guess is that it's waiting for the xp_cmdshell to come back but it's not for some reason.i tested the second stored proc by executing it in sql server express 2005 and it seems to be working just fine. but i thought i should also ask if i can make xp_cmdshell run asynchronously? although the change is not ultimately saved, the trigger does shell out and call the external application...if anyone has any comments (other than this is a really bad way of exchanging data... i know. but this is the solution that was given to me by their support group!)thanks. |
|
|
asgast
Posting Yak Master
149 Posts |
Posted - 2010-05-24 : 04:30:23
|
| I had almost the same problem with one of the software our company has bought. Running some external applications from a trigger is a worst case you can have. I would suggest using after insert trigger with this you can be sure, that at least your data went through. I don't know your setup and problems you are facing, but try to move it out of triggers.I solved my problem by moving xp_cmdshell into a sql job, from the triggered a scheduled this job 5 sec into the future. This worked for me, for some time.You should consider, what will happen with your setup if you'll need to run a lot of this inserts. |
 |
|
|
|
|
|
|
|
xp_cmdshell timing issue... i think.