[dba-SQLServer] Constant Poll: Approaches

Francisco Tapia fhtapia at gmail.com
Tue Jun 10 17:02:33 CDT 2008


What is the maximum amount of latency for the data?

I had a similar operation not too long ago when I still supported our
in-house legacy application.  I created a tempTable for orders.  The purpose
was to enable pre-built in features of Access and was able to bind the table
to a subform.  When a data entry user would type in a new part into the
line, the system would either generate a NEW lineitem ID if the part was for
a new SerialNumber machine, or issue the same LineItemID if the SN was one
that was already typed into the order.

ie:

SN: 123456; RRID: 1000, Qty: 1, Part: ABC
SN: 654321; RRID: 2000, Qty: 1, Part: ABC
SN 123456; RRID: 1000, Qty: 2, Part: XYZ
...etc.

This was accomplished by a trigger on the temptabledetail and
temptableheader.  When an order was completed and submitted, the data was
then sent to a webservice for processing in our legacy system and once the
reply was received it would update all temp details/headers into the main
live tables.

On Tue, Jun 10, 2008 at 9:27 AM, Arthur Fuller <fuller.artful at gmail.com>
wrote:

> I have a pair of tables, call them Fiction and Fact. People around the
> world
> enter rows into Fiction. The database has to process these records and
> depending on some logic, it makes a decision whether or not to enter a
> similar row into Fact. The logic is not important to my question. What I'm
> trying to do is set up a "polling" system so that the engine will examine
> the Fiction table every 10 seconds or so and if there are any new rows,
> fire
> the logic that decides whether to create a row in the Fact table, and then
> timestamp the Fiction row so we know that it's been processed.
>
> One important detail in this operation is that I cannot move to row 2
> before
> completely processing row 1, because the results of row 1 may affect the
> outcome of row 2.
>
> I can think of a couple of approaches -- agent job, trigger, proc with an
> infinite loop. But before I get started coding this, I thought that I'd
> reach out and see if anyone's done something similar and has advice on
> which
> approach might be best.
>
> Thanks in advance for any suggestions.
>
> Arthur
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...



More information about the dba-SQLServer mailing list