none
Running WCF SQL Receive locations on more than once host instance RRS feed

  • Question

  • I’m having some problem understanding how to poll for data using the WCF-Custom adapter.

    I have a fairly simple scenario where I poll for data, extract and mark as extracted. Here is the sanitised SQL;

     

    update  TableA set history_ref ='extracting' where unique_no in (select top 1000 unique_no from TableA	where history_ref = '             ' order by unique_no asc);
    
    select * from TableA where history_ref = 'extracting';
    
    update TableA set history_ref ='extracted' where history_ref = 'extracting';
    
    
    

     

    This runs within the PollingStatement, and I have AmbientTransaction set to true. 

    Now the issue – I have two host instances running our “ReceiveHost” host on 2 different servers, so as I understand it the receive location runs on both. How can I ensure the receive port doesn’t pull back duplicate data? Does this model scale to multiple hosts instances?

    Any help would be appreciated, Thanks!
    Regards, Jason

    Saturday, May 21, 2011 9:27 AM

Answers

  • Hi Jason,

    I would recommend you to use a lock in your stored procedure to prevent other process from reading the same rows, before these have been updated as shown below:-

     

    -- Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION

     

    -- if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

         SET XACT_ABORT ON

     

         update  TableA set history_ref ='extracting' where unique_no in (select top 1000 unique_no from TableA where history_ref = '             ' order by unique_no asc);

         select * from TableA where history_ref = 'extracting';

         update TableA set history_ref ='extracted' where history_ref = 'extracting';

        

         SET XACT_ABORT OFF

    COMMIT TRANSACTION


    Mark As Answer or Vote As Helpful if My Reply Does, Regards, -Rohit
    Saturday, May 21, 2011 8:39 PM
    Moderator
  • Hi Jason,

    you can go through the article http://biztalkworld.wordpress.com/2011/01/05/sql-server-adapter-on-a-clustered-biztalk-environment/

    i have detailed all the information.

     

    -D

     

     

    Sunday, May 22, 2011 4:34 PM

All replies

  • Hi Jason,

    I would recommend you to use a lock in your stored procedure to prevent other process from reading the same rows, before these have been updated as shown below:-

     

    -- Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION

     

    -- if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

         SET XACT_ABORT ON

     

         update  TableA set history_ref ='extracting' where unique_no in (select top 1000 unique_no from TableA where history_ref = '             ' order by unique_no asc);

         select * from TableA where history_ref = 'extracting';

         update TableA set history_ref ='extracted' where history_ref = 'extracting';

        

         SET XACT_ABORT OFF

    COMMIT TRANSACTION


    Mark As Answer or Vote As Helpful if My Reply Does, Regards, -Rohit
    Saturday, May 21, 2011 8:39 PM
    Moderator
  • Hi Jason,

    you can go through the article http://biztalkworld.wordpress.com/2011/01/05/sql-server-adapter-on-a-clustered-biztalk-environment/

    i have detailed all the information.

     

    -D

     

     

    Sunday, May 22, 2011 4:34 PM