"Paul Pedersen" <nospam@no.spam> wrote in message
news:ODXzBElvGHA.5056@TK2MSFTNGP06.phx.gbl...
>I need to have SQL Server monitor a table, and 15 minutes after the last
>update was made, to extract certain data to XML or other text format, then
>send the extract somewhere either via FTP or email.
>
> Can this be done within SQL Server? Or will I need to write an application
> to do it? If it can be done, could someone please give me an outline of
> how to perform this task?
>
> This is MSSQL 2000.
>
>
>

"Bob" <_NO_SPAM_PLEASE_@gmail.com> wrote in message
news:j4KdnUnuWJG80kPZnZ2dnUVZ_omdnZ2d@comcast.com...
> Am still working on a SQL 7 installation, but we had to do something
> similar. Here is how it was implemented:
>
> 1. Create a DATAMONITOR table as follows:
>
> create table DATAMONITOR
> (
>    [DM_ID]                    int                identity,
>    [DM_TIME]              datetime       not null        default
> current_timestamp,
>    [DM_PENDING]      char(1)         not null        default  'Y',
>    [DM_DESC]              varchar(64)
> )
>
> 2. Write an UPDATE, INSERT, and DELETE trigger for the table that inserts
> a record into DATAMONITOR with a brief description of what caused the
> change.
>
> 3. Write a SQL Agent Job that performs a query to check for last change
> being 15 minutes or older with a DM_PENDING value of "Y". If detected,
> change the value to 'N' and perform export steps mentioned. I would use
> something similar to:
>
> IF EXISTS(
>        select *
>        from DATAMONITOR
>        where
>            current_timestamp > dateadd( day, 15, max(DM_TIME))
>            and DM_PENDING = 'Y'
>        )
> begin
>    // ... export code / steps goes here
>
>    // Now reset the flag(s)
>    updated DATAMONITOR
>        set
>            DM_PENDING = 'N'
>        where
>            DM_PENDING = 'Y'
> end
>
>
> 4. Schedule the SQL Agent Job to run every 5 minutes.
>
> This will cause any change to result in performing the export either
> immediately from from 15-20 minutes after the last one occurred. The range
> of 15-20 minutes is typical because we delay a minimum of 15 and the sql
> job runs every 5 minutes.  It could potentially be a minimum of 0 minutes
> in a situation a change is committed immediately between the time we
> detect it is time to process a 15 minute old change and the time we clear
> the flags. In our implementation, this was acceptable since the delay was
> intended to avoid reacting to every single change and that is still
> accomplished.
>
> Note:  there are numerous ways you could do this. You could choose to
> delete the records rather than setting the DM_PENDING value. You could
> always keep a single record in DATAMONITOR and delete the record after
> processing. Note that there are some concurrency issues with this
> solution.
>
>
> "Paul Pedersen" <nospam@no.spam> wrote in message
> news:ODXzBElvGHA.5056@TK2MSFTNGP06.phx.gbl...
>>I need to have SQL Server monitor a table, and 15 minutes after the last
>>update was made, to extract certain data to XML or other text format, then
>>send the extract somewhere either via FTP or email.
>>
>> Can this be done within SQL Server? Or will I need to write an
>> application to do it? If it can be done, could someone please give me an
>> outline of how to perform this task?
>>
>> This is MSSQL 2000.
>>
>>
>>
>
>