Quote:
Originally Posted by jlgottlieb  Hi,
The agent terminates when the exception occurs, but when checking the Shipment_Refnum table, I see that all the refnums that I deleted are still there. I would have expected the deleted refnums to be gone and only the ones after the failed insert to be missing. That does not seem to be the case.
When are the Direct Sql Updates persisted to the database? If the agent fails, are all updates rolled back?
I'm thinking of rewriting the agent to either just update, rather than delete and insert or to write this as a stored procedure to be called instead. I would appreciate any comments as to which is the better way to accomplish this. |
Hi jlgottlieb
It is my practice to always use stored procedures when doing CUD operations. Only for Select statements do I enter it directly in the DirectSQL agent actions.
I found out that although through experience each DirectSQL statement is committed independently, unless we can verify this in the code, it is a risky assumption. Also there is the complexity of multiple agents possibly executing the DirectSQL in parallel...
So at least for me the general rule is this:
1) CUD statements are all executed in stored procedures as a single logical business operation.
2) No COMMITS or ROLLBACKS in the stored procedures
3) Use as few cursors as possible in the stored procedures and when using cursors I always specify EXPLICIT cursors
In addition, to optimise DirectSQL agent actions:
1) If there are multiple SELECT statements with CUD operations needed, all this goes into a single stored procedure
2) Multiple SELECT statements (unless each returning a different result) are collapsed if possible into a SINGLE select statement.
3) Try to push ALL CRUD database operations to the database by way of stored procedures or fuctions - this is to reduce the data round trip required between the application server and DB server.
Hope this helps!
Ian