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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-09-13 : 21:35:45
|
Ralph writes "I have a system that accepts inserts from manufacturing machines. Data flow is driven by the transport application (VB) that reads the machines and inserts the datae into the DB. Each insert sets off a trigger on the input table. The trigger gets gets the unique Machine # and Date_time of last insert from the INSERTED Table. Trigger then calls a scheduling SP with these two parameters. The scheduling SP has an associated table that holds the names of other SP's that need to run in order to process the original input data. The table of scheduled SP's has an OrderIndex column that determines which SP to run first, which second, etc. Each of the scheduled SP's is passed the Machine number and date_time in order to have a handle on which input record to process. This system was desgined to allow quick addition/deletion of the processing steps (Scheduled SP's) - simply requires modifying scheduled SP's table. System 'Worked' OK when there was one VB App inputting data. When we added a second VB App, we started to see deadlocking (we think - servers are 1000 miles away) and ODBC Timeouts every few days, which started when the server would become low on resources dring one of its numerous admin jobs. To remedy this, we created a 2nd DB with ONE table, where the VB App(s) now insert the 'insert text' - they don't actually insert the data, they insert a string, that if run in EXEC(@sqlstring), would insert the data. This table has no triggers nor overhead except an Identity column. The Original database now has a scheduled (once a minute) process that grabs the serial min and max identity values from the new table in the new database and processes this batch one insert at a time. The new process has a 'switch' to turn on/off this queue read. We use this switch from scheduled jobs to stop data flow while jobs are in progress, turn on the flow when jobs are done. Meanwhile, machines can keep on sending data regardless of switch state. Machines have no capacity to wait or queue data - get it or loose it.
Now for the PROBLEM (Thought I'd never get here!) When as error occurs in the reading from the queue or inserting the record to the input table, I can trap errors and keep on rolling - just what I want. But, when an error occurs in the input table trigger or any of the scheduled SP's, it takes down the Queue Read SP, even though it is on the 'otherside' of the input table. This is not fatal to the process, but it delays processing the rest of the queued strings until the next time the Queue Read SP is scheduled (every minute). I assume (no formal training!) this happens because the insert/trigger/schedule sp's all fall within the same transaction to the DB(?).
Is there anyway to build a systematic trap into the prevent the lower level scheduled SP's from aborting the Queue Read. Loss of the offending record that caused the error is not only OK, but preferrable!" |
|
|
|
|
|
|
|
Trapping Errors in SP's called by SP's called by Triggers