ROLL BACK TRANSACTION

[dyarwood]

hi

I'm trying to create a DTS package which on failure will roll back the transaction.

I have the following

BEGIN TRANSACTION

TRUNCATE TABLE mytable

(here is a data pump into a new table)

COMMIT TRANSACTION

The process does not seem to do anything. Does not fail or succeed just hang. Is my thoughts correct that this statement can be used within a DTS package?

 

[gradley]

You should be able to use transaction wrapping in a Execute SQL task of DTS. I'm wondering if you are having trouble because you are using a TRUNCATE TABLE statement. Truncate is not a statement that can be rolled but the DELETE statement can. You may want to try that first.

If this doesn't work, you might try taking out the BEGIN and COMMIT and using the transaction wrapping of DTS as follows:

1. Go to the workflow properties of the task.
2. On the options tab, select "Join transaction..." and "Commit transaction..." checkboxes.
3. On the advanced tab of Package Properties, select "Uses Transactions".

 

[dyarwood]

gradley

You might be on to something with the truncate table. Will give that a go. Cheers