JDBC to proxy scenario --> Update query not working as expected

[former_member184948]

Hi Experts,

We are facing problem in one JDBC to proxy scenario in which we are trying to fetch records from source DB using 'select TOP 500'  and in update we have update top(500) for same condition but from 9500 records only  2300 are coming to target.We also checked that all the records are unique.

select TOP 500  F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.ConcID, F6Accounts.dbo.TOLRecord.eDate, F6Accounts.dbo.TOLRecord.eTime, F6Accounts.dbo.TOLRecord.Extra6,F6Accounts.dbo.TOLRecord.Status from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.eDate ='2014-06-08 00:00:00:000'   AND F6Accounts.dbo.TOLRecord.Extra6 is NULL

update top(500)  F6Accounts.dbo.TOLRecord  set F6Accounts.dbo.TOLRecord.Extra6 = 'X' WHERE F6Accounts.dbo.TOLRecord.eDate ='2014-06-05 00:00:00:000' AND F6Accounts.dbo.TOLRecord.Extra6 is NULL

what is wrong in this? please help.

Here extra6 field act as flag.

[iaki_vila · Accepted solution]

Hi Dlip,

Have you set Transaction Level Isolation = serializable?

Check this blog

Regards.

[former_member184948 · Accepted solution]

Hi Thanks for that but I set that as serializable now but still the problem persist

[former_member184948]

Closing the thread...This issue got resolved.

Had to put more conditions in update query.

Bacially I put same select query in update also as a condition.

select TOP 500  F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.ConcID, F6Accounts.dbo.TOLRecord.eDate, F6Accounts.dbo.TOLRecord.eTime, F6Accounts.dbo.TOLRecord.Extra6,F6Accounts.dbo.TOLRecord.Status from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.Extra6 is NULL  ORDER BY F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.eTime

UPDATE F6Accounts.dbo.TOLRecord SET F6Accounts.dbo.TOLRecord.Extra6 = 'X' WHERE F6Accounts.dbo.TOLRecord.EmpCode in ( select TOP 500  F6Accounts.dbo.TOLRecord.EmpCode from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.Extra6 is NULL ORDER BY F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.eTime) AND F6Accounts.dbo.TOLRecord.eTime in (select TOP 500  F6Accounts.dbo.TOLRecord.eTime from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.Extra6 is NULL ORDER BY F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.eTime)

Thanks for all your helps experts.

[Former Member]

Hi

The values that you gave for eDate in the select/update query are just an example or same values are used in the query? Because i see dates are different in Select and Update query.

'2014-06-08 00:00:00:000'

'2014-06-05 00:00:00:000'

Regards

Osman

[former_member184948]

Hii ..sry.that happened by mistake , actually before posting I forgot to sync both select and update..

[Former Member]

Hi

Try running the select/update queries on the DB table directly and see the result.

Check if it give proper result there.

Regards

Osman