Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbradish
Starting Member
24 Posts |
 Posted - 2011-01-04 : 11:21:40
|
| I am working with a contractor who is resistant to using explicit transactions or BEGIN...END TRY in stored procedures that are being called by a SSIS task. Granted, if the proc fails, SSIS will write to an error log and record the error in one of our Audit SQL tables.This puts me in a position of having to justify error handling inside the stored procedures and quite frankly, it has never occurred to me NOT to trap for errors.I've been reading quite a few articles including SQL Team's "Handling SQL Server Errors", Pinal Dave's blog (http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/), and many (many) others.Does anyone know of a specific article that talks about memory optimization, trans log benefits, auto-commit vs. explicit trans best practices, or error handling in SSIS vs. error handling inside a stored procedure?Please advise."Challenge me. I will not give up. I will not succumb to any situation. I will not be afraid to be great." ~ D. M. Abrashoff |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-04 : 12:19:37
|
| It all depends on what you're doing. Explicit transactions should only be used when absolutely necessary.How you handle errors also depends on what is going on in the procedure. Little point in try/catch for a simple select statement. Depending on what is going on in the stored proc, it is often better to let the client app (SSIS in this case) handle the error. |
 |
|
|
dbradish
Starting Member
24 Posts |
Posted - 2011-01-04 : 13:13:20
|
| The procs that I am referring to are all INSERT, UPDATE or DELETE statements. We do use procs for our SELECTs, but I was not considering those.I have generally used explicit transactions because I may have multiple updates, or wish to do something else other than 'rollback', such as send an e-mail with a sample of the problem data pasted into the message body, or write an audit record to a SQL table.Thanks for the advice. Do any articles come to mind? |
|
|
|
|
|
|
|
|
Stored Proc Trans & Error Handling with SSIS