Hi All,

 We are running the  file generation using Biz Talk. The required data manipulation   done by SQL Stored procedure. Due to some connection issues extract was going to be long running. During that time the SQL transactions not rolled back.   How do we resolve such a transaction related issues, while generating the  file from SQL DB using BizTalk Server.

Thanks,

Hi Thiru,

Due to some connection issues extract was going to be long running. 

Please verify the long running because of database or because of connection issue between BT server and DB server. If transaction is running because of DB then please connect to your DB Administrator for help though if connection issue in BizTalk then I would suggest to use Transaction in BizTalk.

Please refer some useful articles and threads,

BizTalk Transactions by mohamad halabi

HOW TO Retry Atomic Scopes using RetryTransactionException

BizTalk Atomic Transaction Rollback of Variables

Indetail About Atomic Scope / Transactions in BizTalk Server

Atomic Transactions By MSDN

Can you try having a status flag on the SQL table from which you are fetching data. Once data processing is over through BizTalk you can update the relevant field of the SQL table  at last.

This way if your transaction data is failed during processing ,the flag wont be updated and you can get the same data in next polling cycle.

Thanks

Abhishek

Hi,

Are you using any transaction to rollback or you are relying on BizTalk to do it? Once the records are fetched are you updating the records for BizTalk to know which all records have been fetched? If some issue comes while fetching the records the flag will not update.

Rahul