locked
SQLServerProvider RRS feed

  • Question

  • Hi all,

    I'm trying to use SQLServerProvider to looking for new or changed records but it always retrive all the rows. How could I use this provider to retrive only new or changed records?

    <HostedProvider> 
             <ProviderName>SQLActionItemProvider</ProviderName> 
             <ClassName>SQLProvider</ClassName> 
             <SystemName>%_NSSystem_%</SystemName> 
              <Schedule> 
                 <Interval>P0DT00H00M60S</Interval> 
              </Schedule> 
             <Arguments> 
                <Argument> 
                    <Name>EventsQuery</Name> 
                     <Value>SELECT ActionItemOwner, ActionitemDesc, StatusNm FROM [QA_DEV].dbo.ACTIONITEMS_QA_VW</Value> 
                </Argument> 
                <Argument> 
                    <Name>EventClassName</Name> 
                    <Value>ActionItemData</Value> 
                </Argument> 
             </Arguments> 
         </HostedProvider>

    Thank's in advise

    Sunday, September 30, 2012 9:59 AM

Answers

  • Hi Bob,

    The T-SQL statement for the EventsQuery element is what is being executed to retrieve rows from the data source. In your case, the T-SQL statement

    SELECT ActionItemOwner, ActionitemDesc, StatusNm FROM [QA_DEV].dbo.ACTIONITEMS_QA_VW

    is returning all rows, rather than just the new ones.

    To narrow it down, you'll need to modify this statement to recognize only the new ones. One way to do this is to use a chronicles table to keep track of the ones that have been already recognized. I've blogged about that method here.

    Another way is to use the PostQuery element to issue another T-SQL statement after the EventsQuery has run to update the source database (perhaps changing a Processed column from 0 to 1 so that it acts as a flag). I haven't blogged about that mechanism yet.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Iric Wen Thursday, October 4, 2012 1:34 AM
    Monday, October 1, 2012 3:26 AM