December 25, 2007 at 5:20 pm
hi,
forexample i execute a store procedure name sp1 after its commited i execute an other store procedure name spOther severel times (1,2,3....) in a for next clause from vs.net whit the different datas everytime. I want to rollback all the spOther transactions and after that rollback the sp1 if an error occured one of the spOthers. How may i do this. May i save the all transactions with the different names and rollback all of them
December 25, 2007 at 6:01 pm
I'm not sure how this works or if I understand what you're asking. please be a little more clear and give a bit more information in the question.
Are you asking if you
- start a transaction from a .NET application
- call a stored proc that starts and commits a transaction
- call another stored proc that has a separate transaction
- execute a rollback from .NET
what happens? You can start an overriding transaction, AFAIK, from .NET that encompasses your stored procedure transaction. So even though you've committed an inner transaction in step 2, the outer one started in step 1, hasn't committed, so the entire things can be rolled back.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 26, 2007 at 9:54 am
this will use for save a bill by a VS .net program
there are two tables:
1)BILLTABLE
columns:date,CustomerName,BillNumber,BillCost
2)BILLRECORDSTABLE
columns:
BillNumber,RecordNo,ProductName,Quantity,ProductCost,TotalCost
the first Stored Procedure Sp_Bill_Insert is inserting Bill Header data to the BILLTABLE by sqlclient.command, commandtype=adcmd.storedprocedure
the second stored Procedure Sp_BillRecords_Insert is inserting record data to the BILLRECORDSTABLE by sqlclient.command, commandtype=adcmd.storedprocedure in a for next clause to the record counts time
forexample we have a bill for save ad there is 10 records in it
we have to execute 1. store procedure 1 time
and 2. store procedure 10 times for save the Bill
at the second store procedure at 8. record an error occured. So we must the rollback all 8 transactions made by Sp_BillRecords_Insert each time and after that 1 transaction made by Sp_Bill_Insert
is it possible to save the transaction names and transactions on a table and rollback
December 27, 2007 at 12:14 am
A rollback statement will rollback all open transactions on that connection. There's no need to keep track of transaction names and, in fact, naming transactions in SQL is more for readability than anything.
Once a transaction has been committed, it can't be rolled back.
Begin Transaction T1
... do stuff here...
Commit transaction
Rollback transaction T1 -- throws an error - Rollback has no corresponding begin transaction
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2007 at 2:50 pm
Some pseudo code that may help:
You may wish to lower the transaction isolation level depending on your needs. For example, IsolationLevel.RepeatableRead
Also, what kind of error should issue a rollback? A fatal SQL error which will get caught by the SQLException handler. If not, additional code would be needed to read return values and/or output parameters from the stored procedures to determine whether you should commit or rollback.
The stored procedures themselves would have no transaction control--no begins, commits, etc. as the transaction control is being performed by .NET.
Dim txn As SqlTransaction
(dim your sql connection and command objects)
(fill out your sql connection and command objects properties)
Try
conn.Open()
txn = conn.BeginTransaction(IsolationLevel.Serializable)
BillCmd.Transaction = txn
BillDetailCmd.Transaction = txn
BillCmd.ExecuteNonQuery()
loop through your details
BillDetailCmd.ExecuteNonQuery()
next
txn.Commit()
Catch ex As SqlException
txn.Rollback()
Catch ex As Exception
txn.Rollback()
Finally
close the connection and dispose of objects
End Try
Also, please consider changing the names of your stored procedures. Best practices dictate that you should never begin a stored procedure name with sp_. Why? Because sp_ is special and will cause SQL Server to check for a system stored produre with that name first. If found, it will execute the system stored procedure rather than your stored procedure. For performance reasons, it causes a double lookup in the system tables every time. And should Microsoft ever add a system stored procedure with that name, your application will instantly break. A common prefix is usp_ or usp which means user stored procedure. Or simply remove the underscore, spBill_Insert.
Just my two cents,
Joe
December 29, 2007 at 3:10 pm
thank's a lot Joe, i tought in morning to save the transactions with different names and after all saved ,commit or rollback from .net and afternoon i read your message and saw that your codes are more skillfully. I 'll try them in a few days when i have time off from repairing computers ,and thank you very much for your advices, i can not commute your helpful and cents by the thousands of dollars.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply