June 14, 2010 at 6:28 pm
Help needed....
I have created 6 stored procedures and am calling all the 6 stored procedures within SSIS.
Inside each stored procedure am doing a Begin Transaction..
If error occurs rollback transaction and if everything is successful then it commits the transaction...
I get some error messages saying that count of previous transaction is 0 and current transaction is 1...
Do i need to commit and rollback in sql server or does sql server commits and rollback automatically....
Please let me know does begin transaction..rollback transaction and commit transaction does it creates any problem ...as the implementation will be in production database or is it better to have the stored procedure without any rollbacks and commits...
Please help me the above doubt...
June 14, 2010 at 11:05 pm
Lucky9 (6/14/2010)
Help needed....I have created 6 stored procedures and am calling all the 6 stored procedures within SSIS.
Inside each stored procedure am doing a Begin Transaction..
If error occurs rollback transaction and if everything is successful then it commits the transaction...
I get some error messages saying that count of previous transaction is 0 and current transaction is 1...
Looks like in some of the stored procedure you don't have commit transaction, please check it, and if you can post code it will be easy to help
Do i need to commit and rollback in sql server or does sql server commits and rollback automatically....
Please let me know does begin transaction..rollback transaction and commit transaction does it creates any problem ...as the implementation will be in production database or is it better to have the stored procedure without any rollbacks and commits...
Please help me the above doubt...
Using Begin transaction rollback and commit transaction will not create any kind of problem, and it is a better practice to use if you're running more than one insert or updated quires.
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
June 15, 2010 at 12:08 am
It would be advisable to use Transaction while issuing DML operations on the database.
and Begin transaction should follow with either Commit Transaction or Rollback transaction ( in case of any error). Otherwise that operation will not commit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply