[dba-SQLServer] Constant Poll: Approaches (Arthur Fuller)

Hi Arthur:  This is best done using SSIS (formerly DTS).  All the functionality you require is built in to the components.  Once the package is built you would schedule it using an agent job.

HTH.  D. Lewis

------------------------------

Message: 3
Date: Tue, 10 Jun 2008 13:27:44 -0300
From: "Arthur Fuller" <fuller.artful at gmail.com>
Subject: [dba-SQLServer] Constant Poll: Approaches
To: "Discussion concerning MS SQL Server"
        <dba-sqlserver at databaseadvisors.com>
Message-ID:
        <29f585dd0806100927s168fff34j56f39d0539b543ab at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

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


------------------------------


The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission.  If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.