locked
Retrieving same files from sql server RRS feed

  • Question

  • HI experts, I was trying to retrieve the xml content from sql server
    using wcf-sql adapter with biztalk 2009.I succeded in retreiving . but when i see
    the output, biztalk is generating the same xml files again and again,Even my polling statement is simple

    'select columnname from tablename'..a simple SP..Do any one have any idea on this?needed urgently

    Wednesday, August 11, 2010 11:15 AM

Answers

  • The problem is that the SQL adapter will return any row affected by this query. This includes - of course - all already sent rows. If you would add one row at the end you will get all old entries and the new one as BizTalk doesn't care and your SQL statement just says "give me all entries". You need a status flag in your table that you can use in your query.

    After processing data in BizTalk from SQL you definitvely need to update the table:

    1. Delete the processed entries if it's a temp table

      --> SELECT * FROM TABLE

      --> DELETE FROM TABLE

    2. Use a "BTProcessed" field that you can update from 0 to 1

      --> SELECT * FROM TABLE WHERE BTProcessed = 0

      --> UPDATE TABLE SET BTProcessed = 1

    3. Use a entry-creation-date (if exist) for your select

      --> SELECT * FROM TABLE WHERE CreationDate < LastRunDate


    If you like my post or consider it as a valid answer, please use the buttons to show me - Oliver

    http://biztalk.hauth.me

    Thursday, August 12, 2010 4:55 AM

All replies

  • Hi,

    You need to update (by setting a custom status) or delete the records already receive by Biztalk. For more information see section 'Polling for data' at http://www.packtpub.com/article/soa-capabilities-in-bizTalk-wcf

    In the sample an approach to update status field is used.

    Other samples are available here: http://msdn.microsoft.com/en-us/biztalk/dd796258.aspx (topic: SQL Adapter)

    Another option is to query notification. In this case you don't have to poll but receive a notification from SQL when data is available.

     


    HTH,

    Randal van Splunteren - MVP, MCTS BizTalk Server
    http://biztalkmessages.vansplunteren.net

    Please mark as answered if this answers your question.

    Check out the PowerShell provider for BizTalk: http://psbiztalk.codeplex.com
    Wednesday, August 11, 2010 11:38 AM
    Moderator
  • Hi Van,Even i used this query also, when i used this query it returned only top row..

    select xmlfile from xmltable where status=0;update xmltable set status=1 where status=0


    if i use select xmlfile from xmltable,
    It is retriving only top row in multiple times..

    Do u know why its behaving odd?
    Wednesday, August 11, 2010 12:17 PM
  • BizTalk will poll if it finds the data returned from the select query. You must add a column for the status and based on the condition you would poll the data. And after selecting update the column value so next time (after the polling interval) when BizTalk polls the select query would return no data or will return the new data.

    In the polling statement you can write the following query.

    Select * from Table where StatusCol = 'UNREAD'; Update Table set StatusCol = 'READ'


    Abdul Rafay - MVP & MCTS BizTalk Server
    blog: http://abdulrafaysbiztalk.wordpress.com/
    Please mark this as answer if it helps.
    Wednesday, August 11, 2010 2:09 PM
  • HI Rafay,even my query also looks similar na..even if choose select * from table also, am getting only first row..
    Thursday, August 12, 2010 4:15 AM
  • The problem is that the SQL adapter will return any row affected by this query. This includes - of course - all already sent rows. If you would add one row at the end you will get all old entries and the new one as BizTalk doesn't care and your SQL statement just says "give me all entries". You need a status flag in your table that you can use in your query.

    After processing data in BizTalk from SQL you definitvely need to update the table:

    1. Delete the processed entries if it's a temp table

      --> SELECT * FROM TABLE

      --> DELETE FROM TABLE

    2. Use a "BTProcessed" field that you can update from 0 to 1

      --> SELECT * FROM TABLE WHERE BTProcessed = 0

      --> UPDATE TABLE SET BTProcessed = 1

    3. Use a entry-creation-date (if exist) for your select

      --> SELECT * FROM TABLE WHERE CreationDate < LastRunDate


    If you like my post or consider it as a valid answer, please use the buttons to show me - Oliver

    http://biztalk.hauth.me

    Thursday, August 12, 2010 4:55 AM