MSSQL questions

1) I haven't seen any use of "SAVE TRANSACTION" in small to mid size apps. Is it used mainly in big and complex apps?

2) Given this linkhttp://www.databasejournal.com/features/mssql/article.php/3584751 about SQL2005. How can what the article presents be done in SQL2000?

Thanks.

SAVE TRANSACTION isn't normally used in small apps because you would only want to use it in a situation in which you have a subroutine that might fail, but you still want the transaction to commit. Small apps usually either have very small transactions (or none), and you either want them to commit or rollback as a single unit. I've used it in mid size apps where I was looping, doing some processing of for a batch transaction, and any loop may fail, but I needed it to skip the bad loop and continue processing. This isn't very common.

For example a batch load of (something), as you are looping through, Save trans, insert, save trans, insert (Insert fails) rollback, save trans, insert, commit.

Record #1, and #3 are commited to the database, while record #2 failed and was rolled back. Many times this type of logic can also be subdivided into separate transactions (BEGIN TRANS, insert, commit, begin trans, insert (fail), rollback, begin trans, insert, commit), but in some cases it can't. It's those rare cases in which save transaction is nice.

|||Please take a look at my second question and advise? Thanks.|||Can't really help you out there very much. In the past I've written my own .NET apps that monitored directories for file drops and sucked them into the database. Never used the bulk load stuff for that as I don't think it's a great idea to give access to the SQL Server itself for file drops.