February 3, 2010 at 6:36 am
Peter, you need to check XACT_STATE since -1 means the transaction is Doomed and nothing can be done.
wschampheleer, does not solve this issue. What he does is have one transaction in the fist half and if it fails, i creates a while loop and creates one transaction for each row and inserts one row at a time. That is not what I try to accomplish.
What I want to do is this:
* Begin transaction (from .NET code)
* Call a stored procedure for each information that I need to insert/update in the database.
* Have the stored procedure do error checking with try/catch and handle eventual errors in the way I choose.
* When the stored procedure has been call n number of times for each data from my source, I want to decide if I should commit or rollback, depending on how severe I decide the errors are.
One important thing for me in this scenario is that I need to send ALL errors to the data source (in this case, an external company that delivered a data file). If I would just send the first error I find and they send a new file and I get an error on the next line, we would never be finished... so every time I call my stored procedure, it returns a result on this form:
WRN;34;The due_date of the invoice could not be read.
WRN;34;The cost_centre_codes are invalid.
ERR;34;The total amount could not be read.
INF;34;Invoice number 329239832 is not imported, due to at least one error.
The above return string from one call to the stored procedure tells me that the data package had two warnings and one error. In .NET, I simply add this to a list and continues until I have processed all the data from the source.
When all data has been processed, I check the return values to see if I have at least one "ERR". If I do, I rollback the transaction, otherwise I commit.
No matter how things went, I send an e-mail to the source of the data file, to tell them how their data was handled at our end.
This is why SQL Server's implementation of Transactions together with TRY / CATCH does not work for me.
/Ricky
February 3, 2010 at 6:40 am
bitbucket-25253, it is impossible to return to a saved transaction state if SQL Server has decided that the transaction is Doomed/Uncommitable. That is the issue.
February 3, 2010 at 6:47 am
bitbucket-25253 (2/3/2010)
Peter Brinkhaus
I tried a new approach, where I started a nested transaction to solve the problem but since SQL Server does not support rollback of inner transactions without rolling back the outer transactions, it does not work either.
Peter for a very, very simple example of rolling back a nested transaction, using the save_point read this
http://qa.sqlservercentral.com/questions/transactions/68308/ .
hope this will assist in clearing some of the confusion with transactions
Guess you quoted the wrong person here. The point is (a) you cannot rollback to a safepoint if the transaction is uncommittable (b) why would you check in a catch section if the transaction is uncommittable if any error with severity greater than 10 puts the transaction in a uncommitable state.
Peter
February 3, 2010 at 7:05 am
Ricky Helgesson (2/3/2010)
Peter, you need to check XACT_STATE since -1 means the transaction is Doomed and nothing can be done.
I know, but I was wondering if there is any error which doesn't make a transaction uncommittable if even the erroneous assignment to a variable makes it uncommittable. In that case it makes no sense to check the state of the transaction because the transaction will always be doomed. So the question is: which errors do not make a transaction uncommittable.
Peter
February 3, 2010 at 7:11 am
Ricky,
Based on the desciption of your requirements, I would like to share something I have done before in SSIS - maybe it is useful in your situation too.
In the Data Flow Task, as a source I use a stored procedure that reads the data and does all the checks that I want.
The result set contains the original records + two extra columns that I use downstream:
- an indication of whether the record should be rejected or not
- a concatenation of all validation rules that were violated (in your case that would be something like -*WRN1*WRN2*ERR3*INF4*; the numbers refer to a table that contains more information on each validation rule
Next in my Data Flow Task I use a multicast to create two data flows. On each data flow I put a conditional split as a filter. The first flow retains only the records where the rejectflag is not true and inserts them into a table. The second flow captures all the records where at least one validation rule was violated and inserts them into an error table. Afterwards, you can use a split function to join the concatenated error field with the rules table and turn each error into a separate record including a description similar to your example;
You could further enhance this by adding Row Counts to each flow. Based on that you can decide in your Control Flow whether you want to keep the inserted records or not.
HTH
February 3, 2010 at 8:28 am
Thank you all for tips and tricks. We will do one of the following:
1. Move all data verifications to .NET and only call the stored procedure with perfect data.
2. Split the stored procedure in two, one for verification and one for data insert of perfect data (thank you, wschampheleer) AND make sure that the transaction is started before or in the data insert-stored procedure but be sure to not have a transaction running when the data verification stored procedure is run, since that kills the flow.
/Ricky
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply