Using Output Tool to UPDATE SQL table only updates first row

Question

I have a SELECT which gets the primary key for the table and the column I want to update.  It feeds an Output tool which is assigned to the table and the key is defined. When I set it to only WARN on failure, I see that the first row gets updated properly but then each subsequent row I get the following error:

Invalid Cursor state UPDATE dbo.Maintenance_Agreement SET next_billing_date=? WHERE maintenance_agreement_id = ?

Why would the first update work but each subsequent fail?

I have seen in some other posts to put a BEGIN and END in the pre/post SQL.  That gives me an error altogether.

Thoughts?

zmatipano · Answer

I've had this issue before and changing my transaction size to 1 in the output tool solved the problem

I initially had a transaction size of 10,000 and I would get the same issue as you i.e. only the first record being updated.

Let me know if it works for you (it will make your workflow run slower unfortunately)

jmlange18886 · Answer

Attaching screenshots

Output Too Settings.png

Error Message.png

Select results.png