I have been using Batch Update SQL Services for sometime and it works great. Recently I am seen issue with locking.
Here is the example.
I have over 100 records to be updated a given time and if for a reason that record is locked by someother process my adapter service is getting hung.
I am trying to work around this solution. First I was thinking on using transactions but I am not sure how far this will help as its something to do with how adapter will handle transaction which we do not have control over it.
So I was thinking of writing a stored procedure and handle a specific record if that is locked. Here is what I am planning, have a stored procedure which accepts input as array, which inturn checks for any locks and then do a skip lock. This was my SP will have control of processing the records and detect any locks. I was looking at the SP adapter service and trying to figure out easy wasy of converting the list and giving it to SP adapter service. Does anyone have idea on how do I pass the list of values as a cursor to SP adapter service ?