We have a workflow having some actions that performs some external sql server database operation(insert,update
etc) using Call Web Service action.
1. Does Nintex workflow support Transaction ? If sql server database operation transaction fail then we need to rollback everything done by nintex workflow.
2. If not then do you have any suggestion how do we implement transaction having some external sql server database operation in workflow ?
there is no transaction support in workflow. you would need to implement your own error handling.
it's even not a wise idea to include workflow/workflow action into DB transaction since workflow processing is way too slower then DB would need (except of plenty of other serious reasons).
I would suggest that you place all the database operations for the transaction in a stored procedure and execute the stored procedure with a "Execute SQL" action.
We have a few scheduled site workflows that use this approach successfully to update SharePoint lists every 5 minutes with the results of the stored procedures.